SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Making good use of Sysforeignkeys table -- Part 2 Check Table Relation

By Jeffrey Yao,

1. Introduction

When I sometimes have to review the design of a database which has been survived for a long time with various DBAs / developers adding a few tables here, modifying some columns there, and deleting some constraints anywhere, I must say I always try to find as many problems as possible and in a time as short as possible. So I can proudly declare that  this database is no longer worth survival and my employer should be given the contract to rebuild the database for, you know, a big cash. :)  (just kidding, I never risk my reputation.).

Because I want to find out problems ASAP, I like to write small tools to diagnose the database automatically instead of looking at the screen or printouts to locate where problem are. One of the problems is circular reference among tables in a database.

A simplified example about circular reference is as follows:

There are three tables, A, B, C, A is referenced by B, B is referenced by C, while C is referenced by A.
Note: Self reference is not a circular reference.

Though it looks funny that we will ever design a database this way, this is not uncommon in an old database when there are tens, even hundreds of tables evolved gradually with many people involved.

To diagnose circular reference problem, I look to sysforeignkeys table again for help.

2. Implementation

To do a complete diagnosis, I take two steps:

Step 1. Write a stored procedure (SP) usp_ChkCircularRef with a table name as an input parameter, tree the input table as the root of its relationship hierarchy tree which is composed of all dependant tables. If the input table appears during the traversing, at a lower level, it means there exists a loop for circular reference.

Step 2. Compose a script to loop through each distinct referenced table ( (object_name(rkeyid) ) in the sysforeignkeys with a cursor, and call the stored procedure composed in Step 1. A referenced table means it has at least one child table.

Step 2 is easy to implement, so I will not cover it just to save time for you and me. I will only discuss SP in Step 1.

In the SP, I used recursion again as I find sysforeignkeys table is really a good place to practice recursion programming.

2.1 Algorithm Description for usp_ChkCircularRef 

1. If not exists a global temp table ##jy_record (tname varchar(255), seq int), create one, and inserts the first record with the input table name and seq column = 1 (only this first record has seq column value = 1, so I can always find the input table name during the recursion)

2. If the input table has no child table, then exit  else create a cursor to loop through each child.

3. Insert the child table name into the ##jy_record table, compare whether the child table name is the same as the input table name

4. If same, there exists a circular reference error, then prints out the circular path with the records in ##jy_record, else recursively call usp_ChkCircularRef with the child table name as the input parameter

2.2 Script

There is one thing I'd like to mention here, a global temporary table is visible to all sessions, while a local temp table is only visible to the current session. In our case, we need a global temp table that can hold all the inserted record done in each recursion session.

create proc usp_ChkCircularRef
	@ParentName varchar(255)
  ,	@HierarchyLevel tinyint = 0 output -- used for dropping temp table at the end, not necessary in real use
  begin -- sp
      set nocount on
      declare @Child varchar(255), @Parent varchar(255), @Msg varchar(2048)
      if not exists (select * from tempdb.information_schema.tables where table_name='##jy_record')
      begin -- tmp table
          create table ##jy_record (tname varchar(255), seq int default 2 ) 
          insert into ##jy_record (tname, seq) values (@ParentName, 1)
      end -- tmp table
      if not exists (select * from sysforeignkeys where rkeyid = object_id(@ParentName) )
          delete from ##jy_record where seq <> 1 -- if at the end there is no circular ref, delete all records
  -- except the first record which has the original input table 
      select @Parent = tname from ##jy_record where seq = 1
      set @HierarchyLevel = @HierarchyLevel + 1
      declare curTmp cursor local for
      select object_name(fkeyid) as ChildName	from sysforeignkeys 
      where rkeyid = object_id(@ParentName) and rkeyid <> fkeyid -- rkeyid <> fkeyid to omit self ref
      open curTmp
      fetch next from curTmp into @Child
      while @@fetch_status = 0
      begin -- loop
          insert into ##jy_record (tname) values (@Child) -- record the child table
          if @Parent = @Child -- circular ref occur
          begin -- error info
              set @HierarchyLevel = @HierarchyLevel - 1 
              set @Msg = ''
              declare curErr cursor local for select tname from ##jy_record -- for printing out the path
              open curErr
              fetch next from curErr into @Child
              while @@fetch_status = 0
              begin --
  compose circular path
  set @Msg = 	@Child  + '->' + @Msg
  fetch next from curErr into @Child
              end --
  compose circular path
              set @Msg = substring(@Msg, 1, len(@msg)-2 )
  -- delete the tailing "->"
              print 'Error! ' + @Parent + ' has a circular reference: ' + @Msg
              delete from ##jy_record where seq <> 1
  -- delete the current circular path for next loop
              close curErr
              deallocate curErr
          end -- error info
          exec usp_ChkCircularRef @ParentName = @Child,
  @HierarchyLevel = @HierarchyLevel output
          fetch next from curTmp into @Child 
      end -- loop
      close curTmp
      deallocate curTmp
      set @HierarchyLevel = @HierarchyLevel - 1 
      if @HierarchyLevel = 0 
          drop table ##jy_record 
  end -- sp

If nothing returns after running the script, it means there is no circular reference relationship in the database, otherwise, the circular paths will be printed out as error messages, such as "Error! A has a circular reference: A -> B -> C -> A"

3. Summary

This 2nd part article is complimentary to my 1st part, actually if there exists a circular reference relationship in a database, when you run my script in the 1st part, you will get an error, "Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).". But running the script in 2nd part, you will get all the circular reference relationship paths a table may have.

4. Resources

The following is a good article from Michelle A. Pooler



Total article views: 7072 | Views in the last 30 days: 1
Related Articles

Common table expressions and circular references

Finding circular references that are stopping your CTE from working.


copy parent records with child records

copy master/child records in same table


Converting a File with Parent-Child Records in SSIS

Convert a file with multiple record types that have a parent child relationship.


Circular reference

Is it valid design for TableA to reference TableB (foreign key) and TableB to reference back to Tabl...


Circular Reference

Circular Reference I wrote an editorial on the MCA certification referencing a blog by Simon ...

advanced querying