Get metadata for all tables and columns in a SQL Server database

USE <database_name>;
GO
SELECT
    u.name + '.' + t.name AS 'Schema.Table Name' ,
    CAST(td.value AS VARCHAR(5000)) 'Table Description' ,
    c.name AS 'Column Name' ,
    CAST(cd.value AS VARCHAR(5000)) AS 'Column Description'
FROM sysobjects AS t
INNER JOIN sysusers AS u ON u.uid = t.uid
LEFT OUTER JOIN sys.extended_properties AS td ON td.major_id = t.id
    AND td.minor_id = 0
    AND td.name = 'MS_Description'
INNER JOIN syscolumns AS c ON c.id = t.id
LEFT OUTER JOIN sys.extended_properties AS cd ON cd.major_id = c.id
    AND cd.minor_id = c.colid
    AND cd.name = 'MS_Description'
WHERE t.type = 'u'
    AND NOT cd.value IS NULL
ORDER BY t.name, c.colorder
GO

One thought on “Get metadata for all tables and columns in a SQL Server database

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