Script to obtain datadictionary from a SQL2000 db
This script creates a stored procedure that, when executed, queries the system tables of any SQL2000 database to return a complete, ordered datadictionary. *Warning**This script only works on tables which have the description property filled out as my main purpose was to query the extended property description feature new to SQL2000. If you have not filled out any descriptions on your tables or columns, it will not return any data (try completing a couple of column descriptions to test it).
Simply run the query in Query Analyzer (make sure you specify a database) to create the stored procedure. Then call the procedure by typing "exec datadictionary" to return an up to date datadictionary which can be quickly exported to Excel, Word, etc.
Although it is preferred to use the schema views instead of the system tables, this is an easy way to view the table and column description extended properties which are new to SQL 2000. Be aware that when migrating to future versions of SQL, the system tables could change.
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER PROC Datadictionary
--Created by Patty Cline 8/22/01
--Queries table and column properties
--and returns data in an ordered format.
--add comment 'Use [database name]' if you run this against master db
AS
SET NOCOUNT ON
CREATE TABLE #Datadictionary
(table_nm nvarchar(50),
table_id int,
description nvarchar(255),
col_id int,
col_tbl_parent int,
col_lgth int,
col_prec int,
col_scale int,
col_nm nvarchar(50),
col_datatyp nvarchar(50),
col_null nvarchar(50),
col_default nvarchar(50))
--insert column info
INSERT INTO #Datadictionary
(table_nm,table_id,col_id, col_lgth, col_prec, col_scale, col_nm,
col_datatyp, description, col_tbl_parent, col_null, col_default)
SELECT sysobjects.name,syscolumns.id,sysproperties.smallid, syscolumns.length,
syscolumns.prec, syscolumns.scale, syscolumns.name,
systypes.name, Replace(CAST(sysproperties.[value] AS NVARCHAR(255)),',',';'),
syscolumns.id, convert(varchar(254), rtrim(substring
('NO YES',(ColumnProperty (syscolumns.id, syscolumns.name,
'AllowsNull')*3)+1,3))), syscomments.text
FROM sysproperties INNER JOIN sysobjects ON sysproperties.id =
sysobjects.id INNER JOIN syscolumns ON sysproperties.smallid =
syscolumns.colid AND sysobjects.id = syscolumns.id
INNER JOIN systypes ON syscolumns.xtype = systypes.xtype
LEFT OUTER JOIN syscomments ON syscolumns.cdefault =
syscomments.id
--insert table info
INSERT INTO #Datadictionary
(table_nm, table_id, description)
SELECT sysobjects.name, sysobjects.id,
Replace(CAST(sysproperties.[value] AS NVARCHAR(255)),',',';')
FROM sysproperties INNER JOIN sysobjects ON sysproperties.id =
sysobjects.id
WHERE sysproperties.type = 3
--query datadictionary in ordered format
SELECT table_nm, col_nm, description,
col_datatyp, col_lgth, col_null, col_default
FROM #Datadictionary
GROUP BY table_nm, col_nm,description,
col_datatyp, col_lgth, col_null, col_default
ORDER BY table_nm ASC, col_nm ASC
DROP TABLE #Datadictionary
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO 
