Capture Metadata for Data Warehouses using SQL Server Extended Properties

SQL SERVER extended properties allows developers to load metadata information like description, Source etc on a table or column within a table. This will allow information to be documented and attached to SQL SERVER and also queryable.

--Add Extended Property for a table
EXEC sp_addextendedproperty 
 @name=N'Description', 
 @value=N'Dimension with the list of all products.' , 
 @level0type=N'SCHEMA',
 @level0name=N'dbo', 
 @level1type=N'TABLE',
 @level1name=N'DIM_PRODUCTS'

--Update Extended Property for the table
EXEC sp_updateextendedproperty 
 @name=N'Description', 
 @value=N'Dimension with the list of all available products.' , 
 @level0type=N'SCHEMA',
 @level0name=N'dbo', 
 @level1type=N'TABLE',
 @level1name=N'DIM_PRODUCTS'
GO

--Add Extended Property for a column
EXEC sp_addextendedproperty 
 @name=N'Description', 
 @value=N'Name of the Product.' , 
 @level0type=N'SCHEMA',
 @level0name=N'dbo', 
 @level1type=N'TABLE',
 @level1name=N'DIM_PRODUCTS', 
 @level2type=N'COLUMN',
 @level2name=N'PRODUCT_NAME'

--Update Extended Property for a column
EXEC sp_updateextendedproperty 
 @name=N'Description', 
 @value=N'Name of the Product' , 
 @level0type=N'SCHEMA',
 @level0name=N'dbo', 
 @level1type=N'TABLE',
 @level1name=N'DIM_PRODUCTS', 
 @level2type=N'COLUMN',
 @level2name=N'PRODUCT_NAME'
GO

--Query Extended Properties
SELECT objname, name, value
FROM fn_listextendedproperty(NULL, 'schema', 'dbo', 'table', default, NULL, NULL)

SELECT objtype, objname, name, value
FROM fn_listextendedproperty (NULL, 'schema', 'dbo', 'table', 'DIM_PRODUCTS', 'column', default);