| Author |
 Topic  |
|
|
multiplex77
Starting Member
Singapore
24 Posts |
 Posted - 07/31/2007 : 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 - 07/31/2007 : 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
Singapore
24 Posts |
Posted - 07/31/2007 : 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
Flowing Fount of Yak Knowledge
USA
7266 Posts |
Posted - 07/31/2007 : 22:45:49
|
| Use sp_addextendedproperty. |
|
|
|
jezemine
Flowing Fount of Yak Knowledge
USA
2871 Posts |
Posted - 08/01/2007 : 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
Singapore
24 Posts |
Posted - 08/01/2007 : 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 |
|
|
multiplex77
Starting Member
Singapore
24 Posts |
Posted - 08/01/2007 : 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
Flowing Fount of Yak Knowledge
United Kingdom
1064 Posts |
Posted - 08/01/2007 : 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)
USA
6997 Posts |
Posted - 08/01/2007 : 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 - 08/02/2007 : 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
Singapore
24 Posts |
Posted - 08/02/2007 : 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
Flowing Fount of Yak Knowledge
USA
2871 Posts |
Posted - 08/03/2007 : 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 |
|
|
| |
Topic |
|
Adding a Table Description
