Get all of the extended properties for the specified object (Table, View, Function, Procedure).
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 |
DECLARE @object_id INT; SET @object_id = OBJECT_ID('SchemaName.ObjectName'); --<< change to the object that you want to get the extended properties for WITH cte AS ( SELECT * FROM fn_listextendedproperty(NULL, 'SCHEMA', OBJECT_SCHEMA_NAME(@object_id), 'TABLE', OBJECT_NAME(@object_id), DEFAULT, DEFAULT) UNION ALL SELECT * FROM fn_listextendedproperty(NULL, 'SCHEMA', OBJECT_SCHEMA_NAME(@object_id), 'TABLE', OBJECT_NAME(@object_id), 'COLUMN', DEFAULT) UNION ALL SELECT * FROM fn_listextendedproperty(NULL, 'SCHEMA', OBJECT_SCHEMA_NAME(@object_id), 'TABLE', OBJECT_NAME(@object_id), 'CONSTRAINT', DEFAULT) UNION ALL SELECT * FROM fn_listextendedproperty(NULL, 'SCHEMA', OBJECT_SCHEMA_NAME(@object_id), 'TABLE', OBJECT_NAME(@object_id), 'INDEX', DEFAULT) UNION ALL SELECT * FROM fn_listextendedproperty(NULL, 'SCHEMA', OBJECT_SCHEMA_NAME(@object_id), 'PROCEDURE', OBJECT_NAME(@object_id), DEFAULT, DEFAULT) UNION ALL SELECT * FROM fn_listextendedproperty(NULL, 'SCHEMA', OBJECT_SCHEMA_NAME(@object_id), 'PROCEDURE', OBJECT_NAME(@object_id), 'PARAMETER', DEFAULT) UNION ALL SELECT * FROM fn_listextendedproperty(NULL, 'SCHEMA', OBJECT_SCHEMA_NAME(@object_id), 'FUNCTION', OBJECT_NAME(@object_id), DEFAULT, DEFAULT) UNION ALL SELECT * FROM fn_listextendedproperty(NULL, 'SCHEMA', OBJECT_SCHEMA_NAME(@object_id), 'FUNCTION', OBJECT_NAME(@object_id), 'PARAMETER', DEFAULT) UNION ALL SELECT * FROM fn_listextendedproperty(NULL, 'SCHEMA', OBJECT_SCHEMA_NAME(@object_id), 'VIEW', OBJECT_NAME(@object_id), DEFAULT, DEFAULT) UNION ALL SELECT * FROM fn_listextendedproperty(NULL, 'SCHEMA', OBJECT_SCHEMA_NAME(@object_id), 'VIEW', OBJECT_NAME(@object_id), 'COLUMN', DEFAULT) ), cte2 AS ( SELECT Level0Type = 'SCHEMA', Level0Name = OBJECT_SCHEMA_NAME(@object_id), Level1Type = CASE obj.type WHEN 'U' THEN 'TABLE' WHEN 'V' THEN 'VIEW' WHEN 'P' THEN 'PROCEDURE' WHEN 'FN' THEN 'FUNCTION' WHEN 'IF' THEN 'FUNCTION' WHEN 'TF' THEN 'FUNCTION' END, Level1Name = OBJECT_NAME(@object_id), cte.* FROM cte CROSS APPLY (SELECT type FROM sys.objects WHERE object_id = @object_id) obj ) SELECT 'EXECUTE sp_addextendedproperty ' + '@name=' + QUOTENAME(name, CHAR(39)) + ', @value=''' + CONVERT(VARCHAR(7500), value)+ '''' + ', @level0type=' + QUOTENAME(Level0Type,CHAR(39)) + ', @level0name=' + QUOTENAME(Level0Name, CHAR(39)) + ', @level1type=' + QUOTENAME(Level1Type,CHAR(39)) + ', @level1name=' + QUOTENAME(Level1Name, CHAR(39)) + CASE WHEN objtype = Level1Type THEN '' ELSE ', @level2type=' + QUOTENAME(objtype, CHAR(39)) + ', @level2name=' + QUOTENAME(objname, CHAR(39)) END + ';' FROM cte2; |
