Home » C++ ProgrammingRSS

How to generate a script to drop extended properties with the existence check?

Hi everybody,

I want to write a simple script to create my tables. For this I need to drop extended properties (if they exist) and re-create them. I found drop portion script, but it's not clear how to check for the existence of a property in that same script. Quick google search was fruitless as well.

Thanks in advance.

 

 

 

1 Answer Found

 

Answer 1

This script

setnocountonselect'if EXISTS (SELECT 1
FROM ::fn_listextendedproperty (NULL, ''schema'', ''dbo'', ''table'',' + 
quotename(object_name(extended_properties.major_id),'''') + ',NULL,NULL))
  EXEC sp_dropextendedproperty
@name = ' + QUOTENAME(extended_properties.name,'''') + '
,@level0type = ''schema''
,@level0name = ' + object_schema_name(extended_properties.major_id) + '
,@level1type = ''table''
,@level1name = ' + object_name(extended_properties.major_id) + CHAR(13)

from sys.extended_properties

where extended_properties.class_desc = 'OBJECT_OR_COLUMN'and extended_properties.minor_id = 0 andobject_name(extended_properties.major_id) like's[_]%'--and extended_properties.name = 'MS_Description'-- USE AdventureWorks2008R2;--GO--SELECT objtype, objname, name, value--FROM fn_listextendedproperty (NULL, 'schema', 'Production', 'table', 'ScrapReason', 'column', default);--GO--columnsselect'if EXISTS (SELECT 1
FROM ::fn_listextendedproperty (NULL, ''schema'', ''dbo'', ''table'',' + 
quotename(object_name(extended_properties.major_id),'''') + ',''COLUMN'',DEFAULT))
  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'andOBJECT_NAME(object_id) like's[_]%'--select OBJECT_NAME(object_id),* from sys.columns 
didn't quite work as it for some reason truncated level2name in several statements. In any case, I found that I don't really need to generate such statements as DROP TABLE drops them.

 
 
 

<< Previous      Next >>


Microsoft   |   Windows   |   Visual Studio   |   Follow us on Twitter