Database data dictionary

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by rsellers » Database data dictionary

Database data dictionary

Rate Topic

Display Mode

Topic Options

Author

Message

rsellers

Posted Monday, October 08, 2007 7:12 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, November 20, 2012 1:16 PM
Points: 9, Visits: 27

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

Post #407973

Sergey Kazachenko

Posted Thursday, December 13, 2007 1:10 PM

SSC Rookie

Group: General Forum Members
Last Login: Saturday, February 19, 2011 7:19 PM
Points: 31, Visits: 101

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

Post #433050

« Prev Topic | Next Topic »

Permissions