Removing custom properties

  • Thread starter Thread starter Robert Morley
  • Start date Start date
R

Robert Morley

Hi guys,

I know this has been posted before, but I'm damned if I can find it, and
searching for "custom properties" and such on the Internet is like searching
for a specific type of hay in a haystack. Can someone please re-post the
code to remove Access' custom properties, such as Column Width, Order, etc.
from a Table or View in ADP?



Thanks,
Rob
 
I think that MS should provide a tool to manage these extended
properties.

it should be easy to build a simple form that will go through and
delete all of the properties for either a particular object or a
particular type.

-Aaron
 
Okay, so it turns out that what I was looking to remove isn't seen as an
extended property at all. What I'm trying to do is to reset (remove) the
Column Order that Access uses when it opens a view. Apparently SQL Server
sees that as a regular property, even though it pays no attention to the
property at all in the Enterprise Manager. Any thoughts on how to do
that...preferably "en masse"?



Thanks,
Rob
 
It's there. Extended properties can be attached to the view or to a column
of the view. I suppose that you have looked at the wrong place; replace
VIEW1 with the name of your view and take a look:

SELECT *, sql_variant_property(value, 'basetype') AS type FROM
::fn_listextendedproperty(NULL,N'user',N'dbo',N'view',N'VIEW1',NULL,NULL)

SELECT *, sql_variant_property(value, 'basetype') AS type FROM
::fn_listextendedproperty(NULL,N'user',N'dbo',N'view',N'VIEW1',N'column',NULL)
ORDER BY PATINDEX(N'MS_DisplayControl', name) DESC
 
Ah, it's per column...got it. I always thought that was stored at the view
level as an array or string or something.

Any suggestions on how to remove them en masse instead of going one-by-one?



Rob
 
Don't know, as I don't play usually directly with Views under ADP. I
suppose that the best way would be to apply the delete statement to every
View without making the verification if the extended property is there or
not. Shouldn't be to hard to do with a small script and some dynamic sql.
 
It's not an elegant solution that would work for everyone, since it requires
sa privileges, I think, but here's what I did, which worked beautifully:

Allow modifications to system catalogs through properties sheet for server.
DELETE * FROM sysproperties WHERE name = 'MS_ColumnOrder'
Disallow modifications to system catalogs again.



Rob
 
Back
Top