Adding a Table Description - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Adding a Table Description

Author

Topic

multiplex77
Starting Member

24 Posts

Posted - 2007-07-31 : 11:55:08

Hi everyone,

I'm using SQL 2005. I want to add a description for each table on my database. How do I do that? I know I can add a description to each field, but how do I add a description to each TABLE? I tried going to Properties but don't see anything.

Grateful for any help. Thanks.

m.spielkamp
Starting Member

3 Posts

Posted - 2007-07-31 : 17:03:41

you should go to your table and right click, design table, then ,right click ,properties, the last ist Description from this table.Ist there wat you want!

multiplex77
Starting Member

24 Posts

Posted - 2007-07-31 : 21:42:43

Thanks for your reply.

I Right clicked on a table --> Chose Properties

I don't see any field where I can enter a table description under the General tree tab under "Select a page". But when I go to Extended Properties, I see a space for me to enter Name and Value. Is this wat you were referring to?

If so, my next questions is, What is the T-SQL query I use to retrieve this Name-Value pairs?

Thanks for your help.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-07-31 : 22:45:49

Use sp_addextendedproperty.

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-08-01 : 00:19:39

if you want your description to be picked up by other tools, such as EM and SSMS, you should name your property 'MS_Description'

elsasoft.org

multiplex77
Starting Member

24 Posts

Posted - 2007-08-01 : 08:50:51

Thanks for your replies.

I looked up the sp_addextendedproperty documentation again, and it seems I can only add extended properties to a DATABASE or a COLUMN IN A TABLE, but not to A TABLE. Am I wrong? I want to add the description to a TABLE, not to a database or table column.

X002548
Not Just a Number

15586 Posts

Posted - 2007-08-01 : 09:44:36

or just create your own data dictionary tables

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

multiplex77
Starting Member

24 Posts

Posted - 2007-08-01 : 10:00:03

Brett, what do you mean by "create your own data dictionary tables"? I'm trying to generate a data dictionary using a script (which works perfectly, except I can't generate the description of the tables). Of course I can type in the table description on the script's output file, but then in the future when I make changes to the database, I'll have to type everything out again.

So I take it there is NO WAY of adding a description to a TABLE?

pootle_flump

1064 Posts

Posted - 2007-08-01 : 10:01:36

I agree with Brett.

He means create your own tables which contain your table names as values and then have, amongst other things, a description column where you can add your descriptions. This is much more portable and less fiddly than playing around with the SQL Server inbuilt stuff (as you are finding).

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-08-01 : 10:30:12

quote:
Originally posted by multiplex77

Thanks for your replies.

I looked up the sp_addextendedproperty documentation again, and it seems I can only add extended properties to a DATABASE or a COLUMN IN A TABLE, but not to A TABLE. Am I wrong?...

You are wrong.

You can do it with a script.
From SQL Server 2005 Books Online:
Using Extended Properties on Database Objects
Example
In the following example, an extended property is added to the Address table in the Person schema.

USE AdventureWorks;
GO
EXEC sys.sp_addextendedproperty 
@name = N'MS_Description', 
@value = N'Street address information for customers, employees, and vendors.', 
@level0type = N'SCHEMA', @level0name = Person, 
@level1type = N'TABLE',  @level1name = Address;
GO

You can also do it from SSMS in the Table Properties dialog on the Extended properties page.

CODO ERGO SUM

m.spielkamp
Starting Member

3 Posts

Posted - 2007-08-02 : 10:11:05

multiplex77 i´m again here....i will tell you pass to pass....
first in your DB you have much tables, choose one, right click >> DESIGN TABLE
then you must click anywhere with right click >> properties.
the last field is the description from the table.
I hope to have helped !!!

multiplex77
Starting Member

24 Posts

Posted - 2007-08-02 : 10:59:01

Hi everyone,

Thanks heaps for all your replies. I managed to get it to work using your suggestions.

I used the sp_extendedproperty, but changed the name to 'MS_Description_Table' instead of 'MS_Description'. This is so as to distinguish it from the Table.column descriptions. THen I ran my script to pick up that MS_Description_Table. The query looks like this:

SELECT TABLE_NAME, ex2.value AS TABLE_DESCRIPTION FROM INFORMATION_SCHEMA.Tables LEFT JOIN sys.extended_properties ex2 ON ex2.major_id = Object_id(TABLE_NAME) AND ex2.name = 'MS_Description_Table' WHERE TABLE_TYPE = 'BASE TABLE' ORDER BY TABLE_NAME

Thanks for all your help!

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-08-03 : 11:59:47

quote:
Originally posted by multiplex77

Hi everyone,

Thanks heaps for all your replies. I managed to get it to work using your suggestions.

I used the sp_extendedproperty, but changed the name to 'MS_Description_Table' instead of 'MS_Description'. This is so as to distinguish it from the Table.column descriptions. THen I ran my script to pick up that MS_Description_Table. The query looks like this:

SELECT TABLE_NAME, ex2.value AS TABLE_DESCRIPTION FROM INFORMATION_SCHEMA.Tables LEFT JOIN sys.extended_properties ex2 ON ex2.major_id = Object_id(TABLE_NAME) AND ex2.name = 'MS_Description_Table' WHERE TABLE_TYPE = 'BASE TABLE' ORDER BY TABLE_NAME

Thanks for all your help!

There is no need to choose a different name for the property if you are concerned with is collisions with other properties on other objects. They won't collide because @level2type/@level2name are NULL for a table but not null for a column.

I would recommend sticking with MS_Description instead of MS_Description_Table if you care about other tools being able to pick them up.

if you are interested in creating docs for your databases, you might want to check out the app in my sig below.

elsasoft.org

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources