Creating a data dictionary - 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

General SQL Server Forums

Database Design and Application Architecture

Creating a data dictionary

Author

Topic

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2007-10-07 : 23:26:41

I am adding tables to a DB. I want to put table and column descriptions in as I go.

1. Is there a "Description" property for the table? I see in the column properties list a "description".

2. Once I have my descriptions all in, indexes set etc, is there a system stored procedure that I can run that will generate the data dictionay? (With a table description.)

Thanks

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-10-07 : 23:52:18

generally you want to store table and column descriptions in extended properties. you should name the properties 'MS_Description' if you want other tools (such as SSMS) to pick them up.

see the link in my sig for an app that will generate comprehensive data dictionaries for SQL Server and every other DBMS of significiance.

elsasoft.org

Kristen
Test

22859 Posts

Posted - 2007-10-08 : 01:18:57

"Is there a "Description" property for the table?"

Yes, should be on the properties page where you name the table

Kristen

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2007-10-08 : 10:32:05

Kristen,

If I highlight a table then right click and select “Modify” I can select each column and change properties. One of which being description. But I don’t see any table level properties.

If I highlight a table then right click and select “Properties” I see table properties but they are all disabled and there is no description property. (BTW how could i rename a table since all the propertiew including the table name are disabled?) Should I be looking some where else?

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2007-10-08 : 10:40:05

Jezemine,

I should cereate extended properties to carry table and column description information? Then what is te value of the column description property? I should just ignore it?

The 3rd party product you recomended looks good. But with all the emphasis on proper documentation Microsoft (or someone) did not create a simple data dictionary documenter. I do not need any thing elaborate.

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-10-08 : 10:55:41

if you fillin the "description" field for any object in SSMS, what that does under the covers is create an extended property named MS_Description. that's why I said you should name your extended properties that way - if you do, you'll see the descriptions populated in SSMS.

if you don't need anything elaborate, you can probably just write your own script that targets the INFORMATION_SCHEMA views, no?

elsasoft.org

Kristen
Test

22859 Posts

Posted - 2007-10-08 : 11:01:37

"I see table properties but they are all disabled"

Yup, sorry, you need the extended properties

SSMS:

Right click table
Extended properties
"MS_Description" - set to the description of the table

Kristen

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2007-10-08 : 11:26:06

Thank you to both of you. Very helpfull information.

JJBrooks13
Starting Member

2 Posts

Posted - 2007-10-23 : 19:05:33

Look at this product:
http://www.DigitalTools.com

It is an internal web based site that lets you browse your database objects and edit 'MS_Description'.
If nothing else it might give you some ideas.

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-10-24 : 01:49:40

quote:
Originally posted by JJBrooks13

Look at this product:
http://www.DigitalTools.com

It is an internal web based site that lets you browse your database objects and edit 'MS_Description'.
If nothing else it might give you some ideas.

sqlspec does all that, and a lot more. for a lot less $$$.

elsasoft.org

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-24 : 02:20:45

1) Create a table manually
2) Add a description
3) Use the scriptiong features in EM/SSMS to script the table to a query window. Make sure you have set the option to include extended features.
4) Now you have a template how to write it in code.

E 12°55'05.25"
N 56°04'39.16"

JJBrooks13
Starting Member

2 Posts

Posted - 2007-10-25 : 14:21:40

quote:

sqlspec does all that, and a lot more. for a lot less $$$.

With sqlspec it is $149 per user and you run an .Exe program.

With http://www.DigitalTools.com you setup an internal website with unlimited users. So it depends on what you are doing.

X002548
Not Just a Number

15586 Posts

Posted - 2007-10-25 : 14:58:36

Just create your own

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

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-10-26 : 00:53:52

quote:
Originally posted by JJBrooks13

quote:

sqlspec does all that, and a lot more. for a lot less $$$.

With sqlspec it is $149 per user and you run an .Exe program.

With http://www.DigitalTools.com you setup an internal website with unlimited users. So it depends on what you are doing.

actually, a single user can use sqlspec to build a website from which any number of users can edit extended properties on a live server. see: http://www.elsasoft.org/sqlspec.htm#_Toc167981034

elsasoft.org

cyberjunkyks
Starting Member

2 Posts

Posted - 2008-06-06 : 00:39:48

http://www.mssqltips.com/tip.asp?tip=1499

Ken Simmons

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources