Database data dictionary
This script returns a description of the database with references to all tables in the database with keys listed. If the column description has been filled out the listing includes that text.
If you want to get a report on the structure of each database, just add this script to each database, and then use a report writer (like Crystal Reports) to build a printed data dictionary.
CREATE PROCEDURE [DBO].[DatabaseSchema] AS
SELECT Tbl.*,
ISNULL(convert(varchar, Indx.keyno),'') AS KeyNo,
ISNULL( (SELECT name FROM sysindexes WHERE Indx.indid = indid AND Tbl.id = id),'') as IndexName
FROM
sysindexkeys Indx,
(SELECT
OBJ.name AS TableName,
Cols.name AS ColumnName,
(SELECT name FROM systypes WHERE Cols.xusertype = xusertype) AS DataType,
Cols.length,
ISNULL((SELECT CONVERT(varchar(50), value) FROM sysproperties WHERE Cols.id = id AND Cols.colid = smallid),'') AS Description,
ISNULL((SELECT name FROM sysobjects WHERE id = Keys.FKeyID),'') AS FKeyTable,
ISNULL((SELECT name FROM syscolumns WHERE id = Keys.FKeyID AND colid = Keys.FKey),'') AS FKeyColumn,
ISNULL((SELECT name FROM sysobjects WHERE id = Keys.RKeyID),'') AS RKeyTable,
ISNULL((SELECT name FROM syscolumns WHERE id = Keys.RKeyID AND colid = Keys.RKey),'') AS RKeyColumn,
Cols.colorder,
Cols.ID,
Cols.colid
FROM syscolumns Cols,
sysobjects Obj,
sysforeignkeys Keys
WHERE Obj.xtype = 'U' AND
Cols.id = Obj.id AND
((Cols.id *= Keys.fkeyid AND Cols.colid *= Keys.fkey) OR
( Cols.id *= Keys.rkeyid AND Cols.colid *= Keys.rkey))) Tbl
WHERE
tbl.id *= Indx.id AND
tbl.colid *= Indx.colid
ORDER BY Tbl.TableName, Tbl.colorder, Indx.indid, indx.keyno
GO 
