Where is the Description field stored?

[pokasick]

I have a query that I use to display TableName, FieldName, etc for analysis when developing programs. The query pulls the information from the system tables: sysObjects, sysColumns and sysTypes. I want to also pull the description for the field, but I cannot find it in any of the system tables.

I'm assuming that the description is stored in a table like everything in SQL Server is. Just to be clear, I'm using the Design screen in SQL Server Enterprise Manager. Does anyone know where this is stored?

Thanks,
Paul

 

[JayKusch]

Not Really ...

You have to use extended properties to pull a description of an object. You have several SPs to use that can be found in BOL. Some of them would fall under these categories ...

Using Extended Properties on Database Objects

fn_listextendedproperty

sp_addextendedproperty

sp_dropextendedproperty

sp_updateextendedproperty


Thanks

J. Kusch

 

[pokasick]

Thanks for the reply. I'm digging through the BOL and working on figuring out how to use fn_listextendedproperty.

This is supposed to show me all of the Extended Properties, but it returns nothing.

SELECT *
FROM ::fn_listextendedproperty(NULL, NULL, NULL, NULL, NULL, NULL, NULL)

I'm not sure what I'm doing wrong, but I'll keep playing with it for a while.

Paul

 

[JayKusch]

You have to first use the sp_addextendedproperty to give your first object a description. If you want to see the function work quickly, go into enterprise manager and drill down to a table you are familiar with. Open it up in design mode and at the bottom of the screen, you will see a description box that you can enter a description of the first field. as you click on each field in the table, you can add a description. (ps ... the description area is in the same place where you set a seed value and its incremental value).

Once you have a couple of fields "described" go ahead and close/save the table change and run your function.

As a side note ... you can also open up Query Analyzer and choose Tools >> Object Browser >> Show/Hide to enable the Object Browser.

In there you can further set properties on a number of objects. For example, right click on one of your databases shown in QA. choose Extended Properties and Bingo ... your there.

You can also drill down in object browser to the table/view/trigger ... levels and set extended properties as I just stated above.

Enjoy!

Thanks

J. Kusch

 

[pokasick]

I discovered by right-clicking on the field in Query Analyzer, and selecting Extended Properties that the property that I want it called MS_Description.

Using MS_Description in fn_listextendedproperty, I'm able to retrieve what I'm looking for.

Declare @TableName varchar(50)
Declare @ColumnName varchar(50)

Set @TableName = 'authors'
Set @ColumnName = 'au_lname'

SELECT Value
FROM ::fn_listextendedproperty ('MS_Description', 'user', 'dbo',
'table', @TableName, 'column', default)
Where objname = @ColumnName

The declared variables are just so I could change names. Now all I have to do is add it to my query and I'm set. Thanks for your help! I never would have found it.

Paul