Hi all,

I've tried to make an improvement to the management of the extended properties, previously integrated (V314) based on "OmaCoders suggestion to script column extended properties as well" (http://www.sqlservercentral.com/Forums/FindPost1651606.aspx).

The aim is to manage extended properties even for constraint,index,trigger and parameter as well as for column, based on the information obtained by the Adam Aspin's article (http://www.sqlservercentral.com/articles/Metadata/72608/).

Original code:

--OMacoder suggestion for column extended properties http://www.sqlservercentral.com/Forums/FindPost1651606.aspx

SELECT @EXTENDEDPROPERTIES =

@EXTENDEDPROPERTIES + @vbCrLf +

'EXEC sys.sp_addextendedproperty

@name = N''' + [name] + ''', @value = N''' + REPLACE(convert(varchar(max),[value]),'''','''''') + ''',

@level0type = N''SCHEMA'', @level0name = ' + quotename(@SCHEMANAME) + ',

@level1type = N''TABLE'', @level1name = ' + quotename(@TBLNAME) + ',

@level2type = N''COLUMN'', @level2name = ' + quotename([objname]) + ';'

--SELECT objtype, objname, name, value

FROM fn_listextendedproperty (NULL, 'schema', @SCHEMANAME, 'table', @TBLNAME, 'column', NULL)

Now integrated so:

WITH obj AS (

SELECT split.a.value('.', 'VARCHAR(20)') AS name

FROM (

SELECT CAST ('<M>' + REPLACE('column,constraint,index,trigger,parameter', ',', '</M><M>') + '</M>' AS XML) AS data

) AS A

CROSS APPLY data.nodes ('/M') AS split(a)

)

SELECT

@EXTENDEDPROPERTIES =

@EXTENDEDPROPERTIES + @vbCrLf + @vbCrLf +

'EXEC sys.sp_addextendedproperty

@name = N''' + lep.[name] + ''', @value = N''' + REPLACE(convert(varchar(max),lep.[value]),'''','''''') + ''',

@level0type = N''SCHEMA'', @level0name = ' + quotename(@SCHEMANAME) + ',

@level1type = N''TABLE'', @level1name = ' + quotename(@TBLNAME) + ',

@level2type = N''' + UPPER(obj.name) + ''', @level2name = ' + quotename(lep.[objname]) + ';'

--SELECT objtype, objname, name, value

FROM obj

CROSS APPLY fn_listextendedproperty (NULL, 'schema', @SCHEMANAME, 'table', @TBLNAME, obj.name, NULL) AS lep;

Thanks to "bvr" (http://stackoverflow.com/a/16083088) and "Kshitij Satpute" (http://www.sqlservercentral.com/scripts/split+string/100950/) for the delimited string split approach.

Alberto