Extended Properties in SQL Server give you the ablility to attach a comment or description to an object in the database. When you use Management Studio to add a Description to a table or column, it is stored as an Extended Properties with the name of MS_Description, and the object it relates to.

You can use the fn_listextendedproperty() table function to return the extended properties for the database, or a specific table or column. But what if you want to return all comments for all column and tables in the database? Well you’ll need to use the system tables directly to make that happen.

To select all the extended properties for all the columns and tables in the database, use this command:

SELECT OBJECT_NAME(EXP.major_id) AS TableName, 
       C.name                    AS ColumnName, 
       EXP.name                  AS PropertyName, 
       EXP.value                 AS PropertyValue
FROM   sys.extended_properties AS EXP
LEFT OUTER JOIN sys.columns AS C
ON    C.object_id = EXP.major_id
AND   C.column_id = EXP.minor_id
WHERE EXP.class_desc = 'OBJECT_OR_COLUMN'
Tags: ,