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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
description:
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
http://www.geekzilla.co.uk/ViewF3E9658C-7B7A-472C-BE4A-5C25CD26C0E8.htm
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
description:
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
http://www.geekzilla.co.uk/ViewF3E9658C-7B7A-472C-BE4A-5C25CD26C0E8.htm
>> 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..
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..
ASKER
The query listed provides the following error:
Error Source: .Net SqlClient Data Provider
Error Message: invalid object name 'sys.extended_properties'
any ideas?
Error Source: .Net SqlClient Data Provider
Error Message: invalid object name 'sys.extended_properties'
any ideas?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
dzirkelb,
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..
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..
ASKER
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?
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..
I have clicked on Request Attention button so that Moderators can split points on behalf of you.
Kindly take care of this in future..
ASKER
The 2nd link was exactly what I am looking for, thanks!
ASKER