Database data dictionary

  • Comments posted to this topic are about the item Database data dictionary

  • 1) My server was barking on sysproperties table

    2) Why bother creating a stored procedure if it's only reporting in the current database? A simple query would do

    3) The Oracle-ish *= syntax is ugly

    I rewrote the query to use outer joins:

    SELECT Tbl.*,

    ISNULL(convert(varchar, Indx.keyno),'') AS KeyNo,

    ISNULL( (SELECT name FROM sysindexes WHERE Indx.indid = indid AND = id),'') as IndexName


    (SELECT AS TableName, AS ColumnName,

    (SELECT name FROM systypes WHERE Cols.xusertype = xusertype) AS DataType,


    --ISNULL((SELECT CONVERT(varchar(50), value) FROM sysproperties WHERE = 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,




    FROM syscolumns Cols INNER JOIN sysobjects Obj ON

    LEFT OUTER JOIN sysforeignkeys Keys ON

    (( = Keys.fkeyid AND Cols.colid = Keys.fkey) OR

    ( = Keys.rkeyid AND Cols.colid = Keys.rkey))

    WHERE Obj.xtype = 'U'

    ) Tbl

    LEFT OUTER JOIN sysindexkeys Indx ON = AND tbl.colid = Indx.colid

    ORDER BY Tbl.TableName, Tbl.colorder, Indx.indid, indx.keyno

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply