Database data dictionary

• rsellers

  SSC Journeyman

  Points: 81

  More actions

  October 8, 2007 at 7:12 am

  #178175

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

• Sergey Kazachenko

  Ten Centuries

  Points: 1051

  More actions

  December 13, 2007 at 1:10 pm

  #760002

  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