Database data dictionary

  • rsellers

    SSC Journeyman

    Points: 81

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

  • Sergey Kazachenko

    Ten Centuries

    Points: 1051

    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 Tbl.id = id),'') as IndexName

    FROM

    (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 INNER JOIN sysobjects Obj ON cols.id=obj.id

    LEFT OUTER JOIN sysforeignkeys Keys ON

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

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

    WHERE Obj.xtype = 'U'

    ) Tbl

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

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

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

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