• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 522
  • Last Modified:

How to pull Description from a SQL Column

I have a query which pulls some table schema; however, I would like to add the Description of each column to my query results, but I do not know what the correct code is..can anyone help please?  I am using sql-200 as the back end server.
SELECT column_name AS 'Field', data_type AS 'DataType', CHARacter_maximum_length AS 'MaximumLength'
FROM information_schema.columns
WHERE (table_name = 'CustomerContact')
 ORDER BY column_name

Open in new window

  • 4
  • 4
  • 2
2 Solutions
Raja Jegan RSQL Server DBA & ArchitectCommented:
If you have added Column descriptions using sp_addextendedproperty, then you can list it using fn_listextendedproperty function..
More info on these proc and functions here:

dzirkelbAuthor Commented:
I haven't added the descriptions like above, I am just adding them in the Table Design itself.
If you just added them in the Table Design itself:

If you like to view all the descriptions execute the following command.
select object_name(id), name from sysproperties

you can use the following system view to obtain the column
         SELECT *
         FROM   sys.extended_properties
         WHERE  class = 1 AND
                     major_id = object_id(N'dbo.Course') AND
                     name = 'MS_Description'
where "dbo.Course" is one of the tables in the database.

You can have more detail about sys.extended_properties at:
http://msdn.microsoft.com/en-us/library/ms177541.aspx; and
object_id: http://msdn.microsoft.com/en-us/library/ms190328(SQL.90).aspx 

Also check out:
Select Column Information using SQL Server
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

Raja Jegan RSQL Server DBA & ArchitectCommented:
>> I am just adding them in the Table Design itself.

Adding it in Table Design is the GUI way of doing it using procedure sp_addextendedproperty..
And you can use this function to return those Descriptions in a query..
dzirkelbAuthor Commented:
The query listed provides the following error:

Error Source: .Net SqlClient Data Provider
Error Message: invalid object name 'sys.extended_properties'

any ideas?
System stored procedures and functions for working with extended properties
View an extended property: fn_listextendedproperty, sys.extended_properties

Both my queries look to be SQL Server 2000 ones. Correct them with help from:

sys.extended_properties (Transact-SQL)
Schema: How do I show the description property of a column? (for 2000, 2005)
Managing Extended Properties
Reporting Table Descriptions Using sys.extended_properties

Also Check this:
Raja Jegan RSQL Server DBA & ArchitectCommented:

I have been stating from the beginning to use stored procedure sp_addextendedproperty and function fn_listextendedproperty for obtaining Column Description..

But the same when mentioned by k_murli_krishna was accepted as answer without even assisted points which is not totally fair.
Can you kindly justify..
dzirkelbAuthor Commented:
After seeing the solutionI accepted, and then looking at the stored procedures you gave, I can see I should have noticed your solution from teh code given on the page provided.  However, still a semi-rookie to teh site.  How do I adjust points?
Raja Jegan RSQL Server DBA & ArchitectCommented:
Ok.. Not a problem..
I have clicked on Request Attention button so that Moderators can split points on behalf of you.

Kindly take care of this in future..
dzirkelbAuthor Commented:
The 2nd link was exactly what I am looking for, thanks!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 4
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now