This article discusses how to view extended properties in a SQL Server database. Our previous article discussed how to add, edit and delete extended properties. It’s content was based on an article written by Jes Borland at Less Than Dot.

There are three ways to view extended properties in a SQL Server database. The first way is through the GUI of SSMS. That was shown in the previous post and will not be shown here. The two remaining ways are:

• executing fn_listextendedproperty
• querying sys.extended_properties

The second method is preferred because you can get more information out of it.

Below is an sql script that Jes Borland provided on that article.

SELECT EP.class_desc AS PropertyOn, 
    DB_NAME() AS DatabaseName, 
    SCH.name AS SchemaName , 
    TBL.name AS TableName, 
    COL.name AS ColumnName, 
    NULL AS IndexName, 
    NULL AS ProcedureName, 
    NULL AS ParameterName, 
    EP.name AS ExtendedPropertyDescription, 
    EP.VALUE AS ExtendedPropertyValue 
FROM sys.extended_properties AS EP 
    LEFT JOIN sys.TABLES TBL ON TBL.object_id = EP.major_id 
    LEFT JOIN sys.schemas SCH ON SCH.schema_id = TBL.schema_id 
    LEFT JOIN sys.COLUMNS COL ON COL.object_id = TBL.object_id 
        AND COL.column_id = EP.minor_id

WHERE EP.name <> 'MS_Description' 
    --AND EP.class = 1;

Here are some results from SSMS. These results are reflecting the previous post’s adding of extended properties.

Documenting Tables

We have another post called SQL Server Documentation that gives you a query you can use to output information about you tables.