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 ««12

Making Good Use of Sysforeignkeys Table - Part 1: Display table relati Expand / Collapse
Author
Message
Posted Monday, April 24, 2006 1:21 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 05, 2006 7:19 AM
Points: 1, Visits: 1

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)
AS
BEGIN

 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
 RETURN
END

 

Post #274982
Posted Wednesday, May 03, 2006 5:08 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, December 11, 2013 3:04 AM
Points: 210, Visits: 115

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 o
Where OBJECTPROPERTY(o.id, N'IsUserTable') = 1
Order 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 o
Join sys.syscolumns c
  On o.ID = c.ID
Where OBJECTPROPERTY(o.id, N'IsUserTable') = 1
Order 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.name
From sys.foreign_key_columns fkc
Join sys.columns fc
  On fkc.parent_object_id = fc.[object_id]
  And fkc.parent_column_id = fc.column_id
Join 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 @tmpXML
Order By
 sortorder
For 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




Post #277080
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse