October 8, 2007 at 7:12 am
Comments posted to this topic are about the item Database data dictionary
December 13, 2007 at 1:10 pm
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 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply