Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Database data dictionary Expand / Collapse
Author
Message
Posted Monday, October 08, 2007 7:12 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Thursday, December 13, 2007 1:10 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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 »

Add to briefcase

Permissions Expand / Collapse