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
  as 
  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) )
      begin
          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 
          return
      end
  
      select @Parent = tname from ##jy_record where seq = 1
  
      set @HierarchyLevel = @HierarchyLevel + 1
  
      declare curTmp cursor local for
      select object_name(fkeyid) as ChildNamefrom 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
              return
          end -- error info
      else
          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 
  
      return
  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

http://www.winnetmag.com/SQLServer/Article/ArticleID/5448/5448html