Documenting a Database - 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 2000 Forums

SQL Server Administration (2000)

Documenting a Database

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-02-08 : 10:49:37

writes "I have a SQL 7 databse with around 42 tables which is undocumented. Does anyone know a nice simple tool, preferably a freebie, that will produce some decent documentation/data dictionary which can be imported into word"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-02-08 : 10:52:22

It's not a freebie, but if you already have MS Visio there's a "reverse engineer database" function that does a pretty good job. Plus Visio is beautifully integrated with MS Office.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-06 : 12:20:10

You could also use the free database diagram in Enterprise Manager. It allows you to provide descriptions for the tables and columns. Although I would not recommend using this tool so it is pretty limited in functionality, here is some code that I wrote to extract the data dictionary from the diagram:



SET NOCOUNT ON

CREATE TABLE #DataDictionary
(
TableName SYSNAME NOT NULL,
ColumnName SYSNAME NOT NULL,
Description VARCHAR(255) NOT NULL,
ColumnOrder INT NOT NULL
)

INSERT INTO 
	#DataDictionary
SELECT 
	o.name, c.name, CONVERT(VARCHAR(255), p.value), c.colid
FROM 
	syscolumns c, sysproperties p, sysobjects o
WHERE	
	p.smallid = c.colid AND
	c.id = p.id AND 
	c.id = o.id
UNION

SELECT 
	o.name, '', CONVERT(VARCHAR(255), p.value), 0
FROM 
	sysproperties p, sysobjects o
WHERE
	p.id = o.id AND
	p.smallid = 0
ORDER BY
	1, 4

-- This is your Data Dictionary
-- Export to Excel through DTS
SELECT 
	CONVERT(varchar(30), TableName) AS TableName, CONVERT(varchar(40), ColumnName) AS ColumnName, Description
FROM 
	#DataDictionary

DROP TABLE 
	#DataDictionary

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources