SQL Server 2005 - Good SQL to create a data dictionary

Question

I have to create a data dictionary in an existing database for all user tables within that database in SQL Server 2005.

Does anyone have a good piece of sql to use for this purpose. I am intending to add comments in myself after I have the details of the tables and columns.

Answer 1

I don't have the exact sql, but you will need to query the systables and syscolumns tables, which have all of the information you need to create the data dictionary.

Answer 2

I would use the "information schema" views. Here are 2 of the most useful views:

SELECT * FROM INFORMATION_SCHEMA.Tables
SELECT * FROM INFORMATION_SCHEMA.Columns

Answer 3

Instead of doing this yourself, you could take a look at some SQL Server documentation tools/scripts:

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

http://www.red-gate.com/products/SQL_Doc/index.htm

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