Sql Server Column Information

[vivek_29]

I want to find out the description of a column in a table.
Where does the sql stores the various properties of the columns.Such as name,Data-Type,Length,Description etc.

and how can i get it.

Can u help me?

[chanti]

Open a ADODB.recordset for the table whose column's information you wish to know.
You can then use the fields collection of the recordset object
and get all the required information about the columns for that table.

[vivek_29]

Hi thanks for replying.
I know that i can get the various attributes of field by using the method that u have suggested.
But the problem is that i can not get the description of the field.
I want to find the description of the field.

I hope u understand what i m trying to find out.

[chanti]

Hi,
You can use the stored procedure

sp_columns_rowset "table_name"

This is an undocumented stored procedure that is shipped with Sql server versions 7.0 and above.
Try it out if your sql version is 7.0 or above.

[TimCottee]

chanti, nice sp but this still suffers from the same problem as using the ADODB.Connection.OpenSchema method in that the Description field is *always* returned as NULL even if there is a description. I have to say that I have never found a way of retrieving this information using ADO. It may of course be possible using SQL-DMO but I have never tried, as this is the second question this week on this subject perhaps I should have a go just for fun!

[vivek_29]

Hi Chanti,Tim is absolutely correct.The sp that u told suffers from the same problem.

[TimCottee]

Seems like SQL-DMO doesn't support it either; there is no property "Description" in the properties of the column object so it looks like the only way to get this is through enterprise manager which is a bit of a crock really.

[chanti]

Hi,
I got it allast.
Use the sql query show below

SELECT   *
FROM   ::fn_listextendedproperty (NULL, 'user', 'user_name', 'table', 'table_name', 'column', default)

replace the user_name with your sql user name and
table_name with the table to which the column belongs.

This should work guys.
I checked it out.

[TimCottee]

Doesn't return a pre-defined "Description" property from my table, even using all defaults logged in as "sa" I get no values returned but as I haven't added any extended properties I am not really surprised. I still maintain that there is no known way to get the description property, I am ever hopeful of course that somebody will come up with a solution that works (for me at least).