Get All Table & Column Comments

Posted: January 6, 2009 in SQL Server 2005

To list all table and table column comments, open a new query window and execute the following line of code:

-- List all table and column comments from the database
SELECT   [sys].[objects].[name] AS [ObjectName]
        ,[sys].[extended_properties].[value] AS [PropertyValue]
        ,[syscolumns].[name] AS [ColumnName]
FROM     [sys].[objects]
         INNER JOIN [sys].[extended_properties] ON [sys].[objects].[object_id] =
          [sys].[extended_properties].[major_id]
         INNER JOIN [sys].[schemas] ON [sys].[objects].[schema_id] =
          [sys].[schemas].[schema_id]
         LEFT  JOIN [syscolumns] ON [sys].[extended_properties].[minor_id] =
          [syscolumns].[colid]
          AND [sys].[extended_properties].[major_id] = [syscolumns].[id]
WHERE    [sys].[objects].[type]               = 'U'
AND      [sys].[objects].[Name]              <>'sysdiagrams'
AND      [sys].[extended_properties].[name]   = 'MS_Description'
ORDER BY [ObjectName]
        ,[ColumnName];
Advertisements
Comments
  1. […] I create to explain the purpose of each.  maybe I will create a view and modify the adopted code that I found on Amit’s blog.  Thanks Amit! GREAT STUFF!!! SELECT [sys].[objects].[name] AS […]

    • amitchandnz says:

      No worries Matt, glad this post helped you in someway. I use the comments from the fields and tables to generate an automated partial class for use with Dynamic Data which I will post later on.

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