Getting column description in SQL Server 2005

We are working on code generation and thought it would be neat to pull information from description (where we intend to put validations for ex. DOB – No future date).  The following query did that trick.


SELECT
 [Table Name] = i_s.TABLE_NAME,
 [Column Name] = i_s.COLUMN_NAME,
 [Description] = s.value
FROM
 INFORMATION_SCHEMA.COLUMNS i_s
LEFT OUTER JOIN
 sys.extended_properties s
ON
 s.major_id = OBJECT_ID(i_s.TABLE_SCHEMA+'.'+i_s.TABLE_NAME)
 AND s.minor_id = i_s.ORDINAL_POSITION
 AND s.name = 'MS_Description'
WHERE
 OBJECTPROPERTY(OBJECT_ID(i_s.TABLE_SCHEMA+'.'+i_s.TABLE_NAME), 'IsMsShipped')=0
 --AND i_s.TABLE_NAME = 'table_name'
ORDER BY
 i_s.TABLE_NAME, i_s.ORDINAL_POSITION

Hope this helps someone.

Happy Programming!!!

Cheers,

Raja

Advertisements

7 Responses to “Getting column description in SQL Server 2005”


  1. 1 Oscar Quintero June 15, 2010 at 10:44 pm

    Justo lo que necesitaba muchas gracias!!!!!!!!!!!!

  2. 4 Terry Sprague December 1, 2011 at 3:33 pm

    Awesome – this was very helpful!! Thanks!

  3. 5 vhlb72 June 6, 2012 at 9:00 am

    muchas gracias

  4. 7 Masolya October 10, 2012 at 2:22 am

    Thank you. You are the best!


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: