Retrieving description field

[Goalie3533]

For each column name in a sql server table, there are a list of column properties. One of these properties is the "description" property. I need to somehow retrieve the contents of this description and display it on a web page. Does anyone know how I can go about doing this or know of a tutorial that can help me out?

Thanks in advance.

-Goalie35

 

[mrdenny]

You can use fn_listextendedproperty to get the "description" property. I'm not sure of the exact syntax.

To find out, open SQL Profiles, and start a trace, then edit the table in Enterprise Manager, which should pull the data. Then you can dig through the trace and get the correct command.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[philhege]

Here you are. Put the table name in the variable.

declare @l_ctablename varchar(64),
@l_cSQLString varchar(1024)
set @l_ctablename = 'mis_AppSvcRequests'
--objname, value
set @l_cSQLString = '
select column_name, data_type, b.value as description_text from information_schema.columns a
left outer join (SELECT *
FROM ::fn_listextendedproperty (NULL,''user'', ''dbo'', ''table'', ''' + + @l_ctablename
+ ''', ''column'', default)) b on objname = a.column_name
where a.table_name = '''+@l_ctablename+''' '
exec (@l_cSQLString)

You can create an entire data dictionary by wrapping this code in a temp table that is the result of querying the schema for table names.

HTH,

Phil Hegedusich
Senior Web Developer
IIMAK

http://www.iimak.com

-----------
A priest, a lawyer, and a rabbi walk into a bar. The bartender looks up and says "What is this, a joke?