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

Rate

Share

Share

Rate