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

  • jeffrey yao

    SSCarpal Tunnel

    Points: 4244

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

  • robertm

    SSC Eights!

    Points: 986

    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

  • Sameer Raval

    Hall of Fame

    Points: 3214

    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

     

     

     

    Kindest Regards,

    Sameer Raval [Sql Server DBA]
    Geico Insurance
    RavalSameer@hotmail.com

  • jeffrey yao

    SSCarpal Tunnel

    Points: 4244

    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.

  • Kenneth Lee

    Ten Centuries

    Points: 1263

     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.

  • Kenneth Lee

    Ten Centuries

    Points: 1263

    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_name

    as

    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

    go

    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

    as

    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

  • Kenneth Lee

    Ten Centuries

    Points: 1263

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

  • Kenneth Lee

    Ten Centuries

    Points: 1263

    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_name

    as

    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

    go

    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

    as

    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

    -- copied to notepad, then copied it to this post

  • Salvador Anthony Desa

    Grasshopper

    Points: 13

    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.

  • jeffrey yao

    SSCarpal Tunnel

    Points: 4244

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

    Jeffrey

  • Roland Schaer

    Newbie

    Points: 7

    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

     

  • robertm

    SSC Eights!

    Points: 986

    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

Viewing 12 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply