Sql 2K and Data dictionary software

Question

Sql 2K and Data dictionary software

Barkingdog

SSCoach

Points: 18670
More actions
June 13, 2005 at 1:09 pm

#88663

We have lots of databases at my place that aren't well documented -- but at least I've converted them all to Sql 2k level.
Can you recommend any Windows software that generates a data dictionary ("schema") of each item (table, stored proc, etc) in each database? We want to add our own comments describing each field, stored proc so hopefully the output isn’t a read only report! It would also be nice if the software kept text-version snapshots of a database schema (version control?). (Yes, I want my cake and to eat it too.)

TIA,

Bill

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 1

Hi,

For the graphical database diagrams with comments I would use ERwin or ER Studio. For the Data dictionary in the table format itself I would use a simple query from sysobjects and syscolumns, depends on the data dictionary structure you have in mind. This is what I did when I needed a vendor to create a commented data dictionary:

select o.name, c.name, c.xtype, t.name, c.length

from sysobjects o join syscolumns c on o.id=c.id

join systypes t on c.xtype=t.xtype

where o.id>100

AND o.name not like 'dt%'

order by o.name,c.name

Then I copied resulted grid t o Excel and added a Comments field, that theoretically you may include right into Select after c.lenght , something like:

select o.name, c.name, c.xtype, t.name, c.length, ' ' as Comments

Yelena

Regards,Yelena Varsha

sushila SSC-Dedicated Points: 35293 More actions · Answer 2

Far as I know - there does exist a vss/sql server integration for version control - seem to remember reading about it quite a few months back...maybe someone else can confirm this ?!?!

**ASCII stupid question, get a stupid ANSI !!!**

TimS Ten Centuries Points: 1165 More actions · Answer 3

Here's what I started using last week on some table doc I am doing in excel

SELECT

TABLE_NAME, COLUMN_NAME,

COALESCE(DOMAIN_NAME, (DATA_TYPE + COALESCE('(' + NULLIF(CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(4)), '*') + ')', '' ))) AS DATA_TYPE,

IS_NULLABLE, COALESCE(COLUMN_DEFAULT, '') AS COLUMN_DEFAULT

FROM INFORMATION_SCHEMA.COLUMNS col

WHERE TABLE_NAME 'dtproperties'

Tim S

vinny_1973 SSCommitted Points: 1832 More actions · Answer 4

Always wanted to use ERWin ... and as far as i'm aware, it IS one of very few version control systems for DB's on the market. Another option is Visio ... it can take a snapshot of the DB and u can comment etc ... then store the doc in VSS, make changes, re-apply etc! A bit more manual that ERWin, but probably a bit cheaper!?

peterhe SSChampion Points: 11469 More actions · Answer 5

I use SQL server extended property for tables and columns. You can define the properties in Enterprise manager or use exec sp_addextendedproperty manually.

I have a procedure to generate the TSQL statements to for the properties, and another one to generate an xml document about the schema with the extended properties.

If you are interested in this, please check my blog http://spaces.msn.com/members/p-sql/ for details.

tjjfv Valued Member Points: 67 More actions · Answer 6

You may want to take a look at fabForce DBDesigner 4 http://www.fabforce.net/dbdesigner4/. I've only been taking a look at it for a short while, and tested it somewhat with SQL Server, but it seems that it has many of the features you mention, and is rather well developed. The next release will be an official MySQL product, MySQL Workbench.

Barkingdog SSCoach Points: 18670 More actions · Answer 7

Thanks to everyone for their great replies. I have found two other tools (around $125 each) that work with MSSQL and Oracle. Their output is either XML or HTML or Word -- a format suitable for our tech analysts to update the content of each field's "description" field in the Word document. Then we can import these descriptions into the acutal database.

Bill

DTM Schema Reporter: http://www.sqledit.com/sr/?Product

SchemaToDoc: http://www.SchemaToDoc.com/

jezemine SSCrazy Eights Points: 8059 More actions · Answer 8

I know this thread is very old, but I have to add SqlSpec to the list. It's quite a bit better than the two listed above.

Link to it is in my sig below.

---------------------------------------
elsasoft.org

Barkingdog SSCoach Points: 18670 More actions · Answer 9

Barkingdog

SSCoach

Points: 18670

October 27, 2006 at 7:50 pm

#668619

SqlSpec looks good! I'll check it out.

Thanks.