Get a list of all tables in the database with their extended property value.

SELECT
   SCHEMA_NAME(tbl.schema_id) AS SchemaName,	
   tbl.name AS TableName, 
   p.name AS ExtendedPropertyName,
   CAST(p.value AS sql_variant) AS ExtendedPropertyValue
FROM
   sys.tables AS tbl
   INNER JOIN sys.extended_properties AS p ON p.major_id=tbl.object_id AND p.minor_id=0 AND p.class=1

Adding extended property to table.

EXEC sp_addextendedproperty N'MS_Description', N'This table stores data', 'SCHEMA', N'DBO', 'TABLE', N'tblTable01', NULL, NULL
GO
EXEC sp_addextendedproperty 
	@name = N'MS_Description', @value = N'This table stores data', 
	@level0type = 'SCHEMA',    @level0name = N'DBO', 
	@level1type = 'TABLE',     @level1name = N'tblTable01', 
	@level2type = NULL,        @level2name = NULL;
GO

Update existing extended property to table.

EXEC sp_updateextendedproperty N'MS_Description', N'This table stores data', 'SCHEMA', N'DBO', 'TABLE', N'tblTable01', NULL, NULL
GO

Note:

Make sure to use “MS_Description" instead of "Description". This affects how documentation are generated.

Sources:

https://www.mssqltips.com/sqlservertip/5384/working-with-sql-server-extended-properties/

Last modified: February 26, 2020

Author

Comments

Write a Reply or Comment