Another data dictionary

,

Pulls relevant information including col. names, keys, and the description (if the description field is filled out).

This makes a good source for a data dictionary report.

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