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.

2 Responses to Retrieving Table and Column descriptions in SQL Server

  1. Jimmy says:

    SQL Server 2008 R2: If I run one of those statements, I get the following error:
    Msg 102, Level 15, State 1, Line 6
    Incorrect syntax near ‘schema_name’.

  2. frixer says:

    muy buena informacion gracias por compartit tus conocimientos con la comunidad…saludos

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: