I recently needed to script out some extended properties, and create them on a different database.

This might help some of you when migrating, scripting out your objects, …:

SELECT cast('EXEC sp_addextendedproperty N' +char(39)+
cast(ep.name as nvarchar(255))+char(39)+', N'+char(39)+
cast(ep.value as nvarchar(255))+char(39)+', '+ char(39)+'SCHEMA' + char(39)
+ ', N'+char(39)+ cast( S.name as nvarchar(255)) +char(39)+', '+char(39)+ 'TABLE'
+ char(39)+', N'+char(39)+ cast(T.name as nvarchar(255)) +char(39)+', '+char(39)+
'CONSTRAINT' + char(39)+ ', N'+ char(39)+ cast(fk.name as nvarchar(255))
+ char(39) as nvarchar(400))

FROM sys.extended_properties EP

inner join sys.foreign_keys fk on EP.major_id = fk.object_id

inner join sys.tables t on fk.parent_object_id = t.object_id

LEFT JOIN sys.schemas S on fk.schema_id = S.schema_id

LEFT JOIN sys.all_objects O ON ep.major_id = O.object_id



Example of the result:

EXEC sp_addextendedproperty N'FK_CHECK_Filter', N'(1 = 0)', 'SCHEMA', N'stp', 'TABLE', N'CRA', 'CONSTRAINT', N'FK_CRA_Country'

Leave a Comment

Your email address will not be published. Required fields are marked *