locked
Drop extended property "MS_Description" of ALL tables and ALL columns RRS feed

  • Question

  • Hi,

    Is there an easy way (a sql script) to drop the "MS_Description" of all tables and all columns in my database?

    Regards,
    Alejandroo
    Tuesday, September 11, 2007 1:59 PM

Answers

  • These queries will produce a script that you can run to drop these properties.  You can automate with a cursor, or you might want to add a GO to the end of each EXEC statement.

     

    --tables

    select 'EXEC sp_dropextendedproperty

    @name = ''MS_Description''

    ,@level0type = ''schema''

    ,@level0name = ' + object_schema_name(extended_properties.major_id) + '

    ,@level1type = ''table''

    ,@level1name = ' + object_name(extended_properties.major_id)

    from sys.extended_properties

    where extended_properties.class_desc = 'OBJECT_OR_COLUMN'

    and extended_properties.minor_id = 0

    and extended_properties.name = 'MS_Description'

     

    --columns

    select 'EXEC sp_dropextendedproperty

    @name = ''MS_Description''

    ,@level0type = ''schema''

    ,@level0name = ' + object_schema_name(extended_properties.major_id) + '

    ,@level1type = ''table''

    ,@level1name = ' + object_name(extended_properties.major_id) + '

    ,@level2type = ''column''

    ,@level2name = ' + columns.name

    from sys.extended_properties

    join sys.columns

    on columns.object_id = extended_properties.major_id

    and columns.column_id = extended_properties.minor_id

    where extended_properties.class_desc = 'OBJECT_OR_COLUMN'

    and extended_properties.minor_id > 0

    and extended_properties.name = 'MS_Description'

    Tuesday, September 11, 2007 4:47 PM

All replies

  • These queries will produce a script that you can run to drop these properties.  You can automate with a cursor, or you might want to add a GO to the end of each EXEC statement.

     

    --tables

    select 'EXEC sp_dropextendedproperty

    @name = ''MS_Description''

    ,@level0type = ''schema''

    ,@level0name = ' + object_schema_name(extended_properties.major_id) + '

    ,@level1type = ''table''

    ,@level1name = ' + object_name(extended_properties.major_id)

    from sys.extended_properties

    where extended_properties.class_desc = 'OBJECT_OR_COLUMN'

    and extended_properties.minor_id = 0

    and extended_properties.name = 'MS_Description'

     

    --columns

    select 'EXEC sp_dropextendedproperty

    @name = ''MS_Description''

    ,@level0type = ''schema''

    ,@level0name = ' + object_schema_name(extended_properties.major_id) + '

    ,@level1type = ''table''

    ,@level1name = ' + object_name(extended_properties.major_id) + '

    ,@level2type = ''column''

    ,@level2name = ' + columns.name

    from sys.extended_properties

    join sys.columns

    on columns.object_id = extended_properties.major_id

    and columns.column_id = extended_properties.minor_id

    where extended_properties.class_desc = 'OBJECT_OR_COLUMN'

    and extended_properties.minor_id > 0

    and extended_properties.name = 'MS_Description'

    Tuesday, September 11, 2007 4:47 PM
  • Thanks a lot Louis, this works perfectly!
    Wednesday, September 12, 2007 7:38 AM
  • It's a great script, but how can I enhance it with the check for the property existence? 

    Thanks in advance.


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Friday, July 23, 2010 7:48 PM
  • Naom,

    Use something like this:

    IF EXISTS(SELECT * FROM fn_listextendedproperty(N'MyExtendedPropertyName', 'SCHEMA', N'dbo', 'TABLE', N'MyTable', 'COLUMN', N'MyColumn')) EXEC sp_dropextendedproperty N'MyExtendedPropertyName', 'SCHEMA', N'dbo', 'TABLE', N'MyTable', 'COLUMN', N'MyColumn'

    See the documentation for fn_listextendedproperty to find out how to search for extended properties on other object types.

     

    Michael

    Wednesday, September 22, 2010 4:25 AM
  • Yes, thanks, I think this is what I used. But at the end it turned out I didn't need it as all extended properties are dropped when the table is dropped.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Wednesday, September 22, 2010 11:24 AM