Retrieving Table and Column descriptions in SQL Server

SQL Server stores column descriptions as so-called Extended Properties, using the extended property named ‘MS_Description’.

Even though the user interface in Enterprise Manager or Management Studio does not support setting descriptions of tables and other database objects, this is possible using the sp_addextendedproperty and sp_updateextendedproperty stored procedures.

The descriptions added by the developer can be retrieved by the following SQL statements (SQL Server 2005 or higher).

To retrieve the descriptions of all tables:

SELECT sys.objects.name AS TableName, ep.name AS PropertyName,
       ep.value AS Description
FROM sys.objects
CROSS APPLY fn_listextendedproperty(default,
                                    'SCHEMA', schema_name(schema_id),
                                    'TABLE', name, null, null) ep
WHERE sys.objects.name NOT IN ('sysdiagrams')
ORDER BY sys.objects.name

To retrieve the descriptions of all table columns:

SELECT sys.objects.name AS TableName, sys.columns.name AS ColumnName,
       ep.name AS PropertyName, ep.value AS Description
FROM sys.objects
INNER JOIN sys.columns ON sys.objects.object_id = sys.columns.object_id
CROSS APPLY fn_listextendedproperty(default,
                  'SCHEMA', schema_name(schema_id),
                  'TABLE', sys.objects.name, 'COLUMN', sys.columns.name) ep
ORDER BY sys.objects.name, sys.columns.column_id

In SQL Server 2000, you need to call the function ::fn_listextendedproperty, and the CROSS APPLY operation is not supported.