SQLServerCentral / Discuss Content Posted by Jeffrey Yao / Article Discussions / Article Discussions by Author / Making Good Use of Sysforeignkeys Table - Part 1: Display table relati / Latest Posts

RE: Making Good Use of Sysforeignkeys Table - Part 1: Display table relati

robertm — Wed, 03 May 2006 05:08:00 GMT

Right! I thought I'd look at adapting this script yet again to use the new system views in SQL2005, when I started to think that this is a really great idea but it needs to be pulled into the 21st century slightly. What I mean is that if you were serious about using this to document table dependancies, then you'd probably like a little more information and maybe in a slightly pretier format. What I've come up with is a FOR XML EXPLICIT query to retrieve the database metadata as XML and an XSL transformation to represent the table relationships. I think this is the way forward as you could extend the info in the XML as well as reuse existing information to generate other types of reports depending on what it is you want to document.

Therefore, for your reading pleasure please find below the SQL script to create the XML followed by the XSLT file.

PLEASE NOTE: I have spent a minimum of time on this and there may well be some bugs in the final report (I'm by ne means and XSL expert) so please use this at your own risk!!

In saying that, any comments on bugs/improvements that people see would be very happily recieved.

SQL QUERY

Declare @tmpXML Table ( tag int, parent int, [root!1!!element] int, [root!1!databasename] Varchar(255), [root!1!databaseid] int, [tables!2!!element] int, [table!3!!element] Varchar(255), [table!3!id] int, [column!4!!element] Varchar(255), [column!4!id] int, [relationships!5!!element] int, [tablerelationship!6!!element] Varchar(255), [tablerelationship!6!referencedkeytableid] int, [tablerelationship!6!foreignkeytableid] int, [relationship!7!!element] Varchar(255), [relationship!7!id] int, [columnrelationship!8!referencedkeycolumn] Varchar(255), [columnrelationship!8!foreignkeycolumn] Varchar(255), sortorder Varchar(200))

Insert @tmpXML(tag,parent,[root!1!!element],[root!1!databasename],[root!1!databaseid],sortorder)Select 1,0,Null,db_name(), db_id(),0

Insert @tmpXML(tag,parent,[tables!2!!element],sortorder)Select 2,1,Null,0

Insert @tmpXML(tag,parent,[relationships!5!!element],sortorder)Select 5,1,Null,1

Insert @tmpXML ( tag, parent, [table!3!!element], [table!3!id], sortorder)Select 3, 2, name, id, Substring('00000000000', 1, 11 - Len(Cast(id As Varchar))) + Cast(id As Varchar)From sys.sysobjects oWhere OBJECTPROPERTY(o.id, N'IsUserTable') = 1Order By o.name

Insert @tmpXML ( tag, parent, [column!4!!element], [column!4!id], sortorder)Select 4, 3, c.name, c.colid, Substring('00000000000', 1, 11 - Len(Cast(o.id As Varchar))) + Cast(o.id As Varchar) + Substring('0000', 1, 4 - Len(Cast(c.colid As Varchar))) + Cast(c.colid As Varchar)From sys.sysobjects oJoin sys.syscolumns c On o.ID = c.IDWhere OBJECTPROPERTY(o.id, N'IsUserTable') = 1Order By o.name, c.name

Insert @tmpXML ( tag, parent, [tablerelationship!6!!element], [tablerelationship!6!referencedkeytableid], [tablerelationship!6!foreignkeytableid], sortorder)Select Distinct 6, 5, '', parent_object_id, referenced_object_id, Substring('10000000000', 1, 11 - Len(Cast(fk.referenced_object_id As Varchar))) + Cast(fk.referenced_object_id As Varchar) + Substring('10000000000', 1, 11 - Len(Cast(fk.parent_object_id As Varchar))) + Cast(fk.parent_object_id As Varchar)From sys.foreign_keys fk

Insert @tmpXML ( tag, parent, [relationship!7!!element], [relationship!7!id], sortorder)Select Distinct 7, 6, fk.[name], fk.[object_id], Substring('10000000000', 1, 11 - Len(Cast(fk.referenced_object_id As Varchar))) + Cast(fk.referenced_object_id As Varchar) + Substring('10000000000', 1, 11 - Len(Cast(fk.parent_object_id As Varchar))) + Cast(fk.parent_object_id As Varchar) + Substring('00000000000', 1, 11 - Len(Cast(fk.[object_id] As Varchar))) + Cast(fk.[object_id] As Varchar)From sys.foreign_keys fk

Insert @tmpXML ( tag, parent, [columnrelationship!8!referencedkeycolumn], [columnrelationship!8!foreignkeycolumn], sortorder)Select 8, 7, rc.name, fc.name, Substring('10000000000', 1, 11 - Len(Cast(fkc.referenced_object_id As Varchar))) + Cast(fkc.referenced_object_id As Varchar) + Substring('10000000000', 1, 11 - Len(Cast(fkc.parent_object_id As Varchar))) + Cast(fkc.parent_object_id As Varchar) + Substring('00000000000', 1, 11 - Len(Cast(fkc.constraint_object_id As Varchar))) + Cast(fkc.constraint_object_id As Varchar) + rc.nameFrom sys.foreign_key_columns fkcJoin sys.columns fc On fkc.parent_object_id = fc.[object_id] And fkc.parent_column_id = fc.column_idJoin sys.columns rc On fkc.referenced_object_id = rc.[object_id] And fkc.referenced_column_id = rc.column_id

Select tag, parent, [root!1!!element], [root!1!databasename], [root!1!databaseid], [tables!2!!element], [table!3!!element], [table!3!id], [column!4!!element], [column!4!id], [relationships!5!!element], [tablerelationship!6!!element], [tablerelationship!6!referencedkeytableid], [tablerelationship!6!foreignkeytableid], [relationship!7!!element], [relationship!7!id], [columnrelationship!8!referencedkeycolumn], [columnrelationship!8!foreignkeycolumn]From @tmpXMLOrder By sortorderFor XML Explicit

XSL TRANSFORMATION

<?xml version="1.0" encoding="utf-8"?><xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

<xsl:output method="html"/> <xsl:preserve-space elements="text"/> <xsl:template match="column"> <td bgcolor="#fffacd"> <xsl:value-of select="text()"/> </td> </xsl:template> <xsl:template match="table"> <xsl:param name="cnt"/> <xsl:param name="root" select="'0'"/> <xsl:param name="allowselfref" select="'1'"/> <xsl:param name="relationshipid" select="''"/> <xsl:param name="referencedkeytableid" select="''"/>

<xsl:variable name="id" select="@id"/>

<xsl:if test="count(//relationships/tablerelationship[@foreignkeytableid=$id])=0 or $root!=1">

<xsl:variable name="tableformat"> margin-left: 5px; height: 21px; font-size: 10px; font-family: Arial; position: relative; left: <xsl:value-of select="$cnt"/>px; border-left: #000000 1px solid; </xsl:variable>

<table> <xsl:attribute name="style"> <xsl:value-of select="$tableformat"/> </xsl:attribute> <tr height="15px"> <td></td> </tr> <tr> <td> <xsl:for-each select="//relationships/tablerelationship[@referencedkeytableid=$referencedkeytableid][@foreignkeytableid=$id]/relationship"> <xsl:value-of select="text()"/> <span style="font-size: 8px;"> <xsl:value-of select="' (ID:'"/> <xsl:value-of select="@id"/> <xsl:value-of select="')'"/> </span> <xsl:for-each select="columnrelationship"> <xsl:value-of select="' : '"/> <xsl:value-of select="@referencedkeycolumn"/> to <xsl:value-of select="@foreignkeycolumn"/> </xsl:for-each> <br/> </xsl:for-each> </td> </tr> </table> <table> <xsl:attribute name="style"> <xsl:value-of select="$tableformat"/> </xsl:attribute> <tr> <td> <b> <xsl:value-of select="text()"/> </b> <xsl:if test="$allowselfref=0"> <i> <xsl:value-of select="' (Self Referential)'"/> </i> </xsl:if> </td> </tr> </table> <table> <xsl:attribute name="style"> <xsl:value-of select="$tableformat"/> border-bottom: #000000 1px solid; </xsl:attribute> <tr> <xsl:apply-templates select="column"/> </tr> </table>

<xsl:for-each select="//relationships/tablerelationship[@referencedkeytableid=$id]">

<xsl:variable name="foreignkeytableid" select="@foreignkeytableid"/>

<xsl:if test="$id!=$foreignkeytableid and $allowselfref=1"> <xsl:apply-templates select="//tables/table[@id=$foreignkeytableid]"> <xsl:with-param name="cnt" select="$cnt+50"/> <xsl:with-param name="allowselfref" select="1"/> <xsl:with-param name="relationshipid" select="@id"/> <xsl:with-param name="referencedkeytableid" select="@referencedkeytableid"/> </xsl:apply-templates> </xsl:if> <xsl:if test="$id=$foreignkeytableid and $allowselfref=1"> <xsl:apply-templates select="//tables/table[@id=$foreignkeytableid]"> <xsl:with-param name="cnt" select="$cnt+50"/> <xsl:with-param name="allowselfref" select="0"/> <xsl:with-param name="relationshipid" select="@id"/> <xsl:with-param name="referencedkeytableid" select="@referencedkeytableid"/> </xsl:apply-templates> </xsl:if>

</xsl:for-each> </xsl:if> </xsl:template> <xsl:template match="tables"> <xsl:apply-templates select="table"> <xsl:with-param name="cnt" select="0"/> <xsl:with-param name="root" select="1"/> </xsl:apply-templates> </xsl:template> <xsl:template match="root"> <html> <body> <pre> <xsl:apply-templates select="tables"/> </pre> </body> </html> </xsl:template>

</xsl:stylesheet>

At the moment the XSL creates a new tree for every table in the database that doesn't exist as a foreign key in another relationship. You could easily change the filter on the call to the table template to report on a specific table if you liked. Also this only reports from the parent down but once again with a few edits you could extend the XSL to work in the other direction.

Happy documenting

Rob

RE: Making Good Use of Sysforeignkeys Table - Part 1: Display table relati

Roland Schaer — Mon, 24 Apr 2006 13:21:00 GMT

In case anyone is interested, Listed below is a bottom-up version of the proc:

CREATE PROCEDURE dbo.DisplayBottomUpTableHierarchy( @TableName VARCHAR(128), @SpaceLen INT = -4 OUTPUT)ASBEGIN

DECLARE @Child VARCHAR(100) SET @SpaceLen = @SpaceLen + 4 IF @TableName IS NOT NULL BEGIN IF EXISTS(SELECT * FROM sysforeignkeys WHERE fkeyid = OBJECT_ID(@TableName)) BEGIN IF @SpaceLen <= 0 PRINT @TableName ELSE PRINT SPACE(@SpaceLen) + '|---' + @TableName IF EXISTS(SELECT * FROM sysforeignkeys WHERE fkeyid = OBJECT_ID(@TableName) AND fkeyid = rkeyid) PRINT SPACE(@SpaceLen) + '|---' + @TableName DECLARE curChild CURSOR LOCAL FOR SELECT OBJECT_NAME(rkeyid) AS child FROM sysforeignkeys WHERE fkeyid = OBJECT_ID(@TableName) AND fkeyid <> rkeyid OPEN curChild FETCH NEXT FROM curChild INTO @Child WHILE @@fetch_status = 0 BEGIN EXEC DisplayBottomUpTableHierarchy @TableName = @Child, @SpaceLen = @SpaceLen OUTPUT FETCH NEXT FROM curChild into @Child END CLOSE curChild DEALLOCATE curChild END ELSE BEGIN IF @SpaceLen <= 0 PRINT @TableName ELSE PRINT SPACE(@SpaceLen) + '|---' + @TableName END END SET @SpaceLen = @SpaceLen - 4 RETURNEND

RE: Making Good Use of Sysforeignkeys Table - Part 1: Display table relati

jeffrey yao — Thu, 05 Jan 2006 10:58:00 GMT

Thanks, Kenneth for your great work to help improve my code. Really appreciate it !

Jeffrey

RE: Making Good Use of Sysforeignkeys Table - Part 1: Display table relati

Salvador Anthony Desa — Fri, 21 Oct 2005 07:42:00 GMT

Thank you so much for such a wonderfull piece of code. I was working on a project and what you have in your code is exactly what I was looking for...

Its really a very good piece of code to keep you document upto date with minmum efforts.

RE: Making Good Use of Sysforeignkeys Table - Part 1: Display table relati

Kenneth Lee — Wed, 01 Jun 2005 15:01:00 GMT

if NOT exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_DisplayTableRelation]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) exec sp_executesql N'CREATE procedure usp_DisplayTableRelation AS print 1'if NOT exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_DisplayTableRelation1]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) exec sp_executesql N'CREATE procedure usp_DisplayTableRelation1 AS print 1'go

alter procedure usp_DisplayTableRelation @table_name nvarchar(128) -- the procedure will try to find all the child tables for the table @table_nameasbegin -- sp if @table_name is null return if not exists (select * from tempdb.dbo.sysobjects where name = N'##UsedTableName' ) create table ##UsedTableName (TableName nvarchar(128))-- use to track recursive calls to same table else delete from ##UsedTableName if not exists (select * from tempdb.dbo.sysobjects where name = N'##DisplayTableRelation' ) create table ##DisplayTableRelation (Relation nvarchar(4000))-- use to track recursive calls to same table else delete from ##DisplayTableRelation

exec usp_DisplayTableRelation1 @table_name = @table_name

select * from ##DisplayTableRelation drop table ##UsedTableName drop table ##DisplayTableRelation returnend --spgo

alter procedure usp_DisplayTableRelation1 @table_name nvarchar(128) -- the procedure will try to find all the child tables for the table @table_name, @space_len int = -4 output -- for insert into ##DisplayTableRelation (Relation) selecting position purposeasbegin -- sp declare @child nvarchar(128) declare @usedTable nvarchar(100) if @table_name is null return if not exists (select * from tempdb.dbo.sysobjects where name = N'##DisplayTableRelation' ) begin exec usp_DisplayTableRelation @table_name return end set @space_len = @space_len + 4

if not exists (select * from sysforeignkeys where rkeyid = object_id(@table_name) ) begin -- leaf level if @space_len <= 0 insert into ##DisplayTableRelation (Relation) select @table_name else insert into ##DisplayTableRelation (Relation) select space(@space_len) + '|---' + @table_name

set @space_len = @space_len - 4 return end -- leaf level else -- the @table_name table exists begin -- else set @usedTable = N'' if exists (select * from ##UsedTableName where TableName = @table_name) set @usedTable = N' (Loop Reference)' else insert into ##UsedTableName (TableName) select @table_name if @space_len <= 0 insert into ##DisplayTableRelation (Relation) select @table_name + @usedTable else insert into ##DisplayTableRelation (Relation) select space(@space_len) + '|---' + @table_name + @usedTable

if (@usedTable = N'' --first time and exists ( select * from sysforeignkeys where rkeyid = object_id(@table_name) and rkeyid = fkeyid)) -- self referenced insert into ##DisplayTableRelation (Relation) select space(@space_len+4) + '|---' + @table_name

declare curChild cursor local for select object_name(fkeyid) as child from sysforeignkeys where rkeyid = object_id(@table_name) and rkeyid <> fkeyid

open curChild fetch next from curChild into @child while @@fetch_status = 0 begin -- cursor loop if (@usedTable = N'') exec usp_DisplayTableRelation1 @table_name = @child, @space_len = @space_len output fetch next from curChild into @child end -- cursor loop

close curChild deallocate curChild end --else set @space_len = @space_len - 4 returnend --sp-- copied to notepad, then copied it to this post

RE: Making Good Use of Sysforeignkeys Table - Part 1: Display table relati

Kenneth Lee — Wed, 01 Jun 2005 14:59:00 GMT

Sorry about the double spacing. That's this posting routine getting involved.

RE: Making Good Use of Sysforeignkeys Table - Part 1: Display table relati

Kenneth Lee — Wed, 01 Jun 2005 14:58:00 GMT

if NOT exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_DisplayTableRelation]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

exec sp_executesql N'CREATE procedure usp_DisplayTableRelation AS print 1'

if NOT exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_DisplayTableRelation1]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

exec sp_executesql N'CREATE procedure usp_DisplayTableRelation1 AS print 1'

alter procedure usp_DisplayTableRelation

@table_name nvarchar(128) -- the procedure will try to find all the child tables for the table @table_name

begin -- sp

if @table_name is null

return

if not exists (select * from tempdb.dbo.sysobjects where name = N'##UsedTableName' )

create table ##UsedTableName (TableName nvarchar(128))-- use to track recursive calls to same table

else delete from ##UsedTableName

if not exists (select * from tempdb.dbo.sysobjects where name = N'##DisplayTableRelation' )

create table ##DisplayTableRelation (Relation nvarchar(4000))-- use to track recursive calls to same table

else delete from ##DisplayTableRelation

exec usp_DisplayTableRelation1 @table_name = @table_name

select * from ##DisplayTableRelation

drop table ##UsedTableName

drop table ##DisplayTableRelation

return

end --sp

alter procedure usp_DisplayTableRelation1

@table_name nvarchar(128) -- the procedure will try to find all the child tables for the table @table_name

, @space_len int = -4 output -- for insert into ##DisplayTableRelation (Relation) selecting position purpose

begin -- sp

declare @child nvarchar(128)

declare @usedTable nvarchar(100)

if @table_name is null

return

if not exists (select * from tempdb.dbo.sysobjects where name = N'##DisplayTableRelation' )

begin

exec usp_DisplayTableRelation @table_name

return

end

set @space_len = @space_len + 4

if not exists (select * from sysforeignkeys where rkeyid = object_id(@table_name) )

begin -- leaf level

if @space_len <= 0

insert into ##DisplayTableRelation (Relation) select @table_name

else

insert into ##DisplayTableRelation (Relation) select space(@space_len) + '|---' + @table_name

set @space_len = @space_len - 4

return

end -- leaf level

else -- the @table_name table exists

begin -- else

set @usedTable = N''

if exists (select * from ##UsedTableName where TableName = @table_name)

set @usedTable = N' (Loop Reference)'

else insert into ##UsedTableName (TableName) select @table_name

if @space_len <= 0

insert into ##DisplayTableRelation (Relation) select @table_name + @usedTable

else

insert into ##DisplayTableRelation (Relation) select space(@space_len) + '|---' + @table_name + @usedTable

if (@usedTable = N'' --first time

and exists ( select * from sysforeignkeys where rkeyid = object_id(@table_name) and rkeyid = fkeyid)) -- self referenced

insert into ##DisplayTableRelation (Relation) select space(@space_len+4) + '|---' + @table_name

declare curChild cursor local for

select object_name(fkeyid) as child from sysforeignkeys

where rkeyid = object_id(@table_name) and rkeyid <> fkeyid

open curChild

fetch next from curChild into @child

while @@fetch_status = 0

begin -- cursor loop

if (@usedTable = N'') exec usp_DisplayTableRelation1 @table_name = @child, @space_len = @space_len output

fetch next from curChild into @child

end -- cursor loop

close curChild

deallocate curChild

end --else

set @space_len = @space_len - 4

return

end --sp

RE: Making Good Use of Sysforeignkeys Table - Part 1: Display table relati

Kenneth Lee — Wed, 01 Jun 2005 14:57:00 GMT

I immediately blew up on a recursive loop count error, basically because your code can put the table references in an infinite loop. Here's an extract of the output after I modified your code. |---Contact |---Contact |---MortgageInsurance |---ContactOffice |---LoanFeeTemplate |---Contact (Loop Reference)First I put in a temp table to track what tables have already had tree output. Oh, yuck, now there's all these add row messages in the middle of your print. OK add a second table to hold the output. Hmmm, now there's maintenance of these temp tables and no way for your recursive loop to know when the table is done. Make two procs, one to handle the tables and one to recursively loop through the list. Hmmm, your self-refference is on the same level, may as well fix that as well. I'll put my finished code (that also takes care of the error message your newbie had.) in a new post.

RE: Making Good Use of Sysforeignkeys Table - Part 1: Display table relati

jeffrey yao — Wed, 01 Jun 2005 14:06:00 GMT

Thanks, Robertm, for pointing out the bug. Really appreciate it !

Sameer, I have replied your email. Pls check it.

This SP will only display the relationship tree from the top to bottom. So if your table is at on the leaf level of a tree, the SP will only display the table itself.

In future, I may revise this SP by adding a "switch" control to allow display a "reverse" relationship tree, i.e. from the bottom to top.

I am honored if my script can be of any help to SCC community.

RE: Making Good Use of Sysforeignkeys Table - Part 1: Display table relati

Sameer Raval — Wed, 01 Jun 2005 07:23:00 GMT

This looks to be very good SP.

I have small problem creating this sp , get following message

Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'usp_DisplayTableRelation'. The stored procedure will still be created.

Still, I continued but not resulting anything else then tblname that is input.

Will appriciate your help to resolve the problem..

Thanks,

Sameer

RE: Making Good Use of Sysforeignkeys Table - Part 1: Display table relati

robertm — Wed, 01 Jun 2005 03:27:00 GMT

This is a really great use of data in the sys tables of your database, especially when it comes to providing documentation.

One small bug in the procedure however is that it will duplicate parent child relationships in the resultant tree between two tables where the relationship is composed of a composite key. To rectify this you need to simply pouplate the cursor curChild with a distinct list of foreign keys, e.g.

declare curChild cursor local for select distinct object_name(fkeyid) as child from sysforeignkeys where rkeyid = object_id(@table_name) and rkeyid <> fkeyid

Making Good Use of Sysforeignkeys Table - Part 1: Display table relati

jeffrey yao — Wed, 26 May 2004 09:53:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral