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'