How to find circular dependent table in sql server

Question

Currently im working on finding the dpendency order of tables in a database. And im stuck up with peoblem of circular depedency of some tables in database.

since some tables are circularly depended im not getting the entire order.....

Is there any way to find circular dependent tables in any database in sql server, Other than the database diagrams??

Answer 1

You don't really need to buy a tool to find these references.

SELECT 
  OBJECT_SCHEMA_NAME(fk1.parent_object_id)
   + '.' + OBJECT_NAME(fk1.parent_object_id), 
  OBJECT_SCHEMA_NAME(fk2.parent_object_id)
   + '.' + OBJECT_NAME(fk2.parent_object_id)
FROM sys.foreign_keys AS fk1
INNER JOIN sys.foreign_keys AS fk2
ON fk1.parent_object_id = fk2.referenced_object_id
AND fk2.parent_object_id = fk1.referenced_object_id;

Answer 2

I stumbled across I script that I have now found reproduced in several places. I think it original came from the SQL Azure Team Blog in a post from 2010 about:

In the world of relational databases circular references are schema structures where foreign keys relating the tables create a loop. Circular references cause special types of issues when trying to synchronize two relational database where the foreign keys are enforced. Because of this issue, database schemas that contain circular references are restricted in the tools that can be used when synchronizing and replicating the database. This article will explain circular references and demonstrate a Transact-SQL script for determining if your database has a circular reference.

It is also reproduced here and credited to Wayne Berry. Maybe he was on the Sql Azure team?

@Aaron_Bertrand's answer excellent and concise. For the sake of completeness, I think it is worth adding this script since it finds longer dependency chains. The link was hard enough to find that I will reproduce the code here rather than just the link hopes of making it easier for the next guy.

It is not concise.

The Transact-SQL script below uses a recursive cursor to detect if there are any circular references in your database schema. It can be run on your SQL Server database before you try to synchronize it with SQL Azure, or you can run it on your SQL Azure database. You can run it in the Query Window of SQL Server Management Studio; the output will be displayed as in the Message section.

If you have circular references the output will look like this:

dbo.City -> dbo.Author -> dbo.City dbo.Division -> dbo.Author -> dbo.City -> dbo.County -> dbo.Region -> dbo.Image -> dbo.Division dbo.State -> dbo.Image -> dbo.Area -> dbo.Author -> dbo.City -> dbo.County -> dbo.Region -> >dbo.State dbo.County -> dbo.Region -> dbo.Author -> dbo.City -> dbo.County dbo.Image -> dbo.Area -> dbo.Author -> dbo.City -> dbo.County -> dbo.Region -> dbo.Image dbo.Location -> dbo.Author -> dbo.City -> dbo.County -> dbo.Region -> dbo.Image -> dbo.Location dbo.LGroup -> dbo.LGroup dbo.Region -> dbo.Author -> dbo.City -> dbo.County -> dbo.Region dbo.Author -> dbo.City -> dbo.Author dbo.Area -> dbo.Author -> dbo.City -> dbo.County -> dbo.Region -> dbo.Image -> dbo.Area

Each line is a circular reference, with a link list of tables that create the circle. The Transact-SQL script to detect circular references is below...This code will work on SQL Azure and SQL Server.

SET NOCOUNT ON

-- WWB: Create a Temp Table Of All Relationship To Improve Overall Performance
CREATE TABLE #TableRelationships (FK_Schema nvarchar(max), FK_Table nvarchar(max),
    PK_Schema nvarchar(max), PK_Table nvarchar(max))

-- WWB: Create a List Of All Tables To Check
CREATE TABLE #TableList ([Schema] nvarchar(max), [Table] nvarchar(max))

-- WWB: Fill the Table List
INSERT INTO #TableList ([Table], [Schema])
SELECT TABLE_NAME, TABLE_SCHEMA
FROM INFORMATION_SCHEMA.TABLES 
WHERE Table_Type = 'BASE TABLE'

-- WWB: Fill the RelationShip Temp Table
INSERT INTO #TableRelationships(FK_Schema, FK_Table, PK_Schema, PK_Table)
SELECT
    FK.TABLE_SCHEMA,
    FK.TABLE_NAME,
    PK.TABLE_SCHEMA,
    PK.TABLE_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
      INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON 
        C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
      INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON 
        C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
      INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON 
        C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
      INNER JOIN (
            SELECT i1.TABLE_NAME, i2.COLUMN_NAME
            FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
            INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON
             i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
            WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT ON PT.TABLE_NAME = PK.TABLE_NAME

CREATE TABLE #Stack([Schema] nvarchar(max), [Table] nvarchar(max))

GO

-- WWB: Drop SqlAzureRecursiveFind
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = 
    OBJECT_ID(N'[dbo].[SqlAzureRecursiveFind]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SqlAzureRecursiveFind]

GO

-- WWB: Create a Stored Procedure that Recursively Calls Itself
CREATE PROC SqlAzureRecursiveFind
    @BaseSchmea nvarchar(max),
    @BaseTable nvarchar(max),
    @Schmea nvarchar(max),
    @Table nvarchar(max),
    @Fail nvarchar(max) OUTPUT
AS

    SET NOCOUNT ON

    -- WWB: Keep Track Of the Schema and Tables We Have Checked
    -- Prevents Looping          
    INSERT INTO #Stack([Schema],[Table]) VALUES (@Schmea, @Table)

    DECLARE @RelatedSchema nvarchar(max)
    DECLARE @RelatedTable nvarchar(max)

    -- WWB: Select all tables that the input table is dependent on
    DECLARE table_cursor CURSOR LOCAL  FOR
          SELECT PK_Schema, PK_Table
          FROM #TableRelationships
          WHERE FK_Schema = @Schmea AND FK_Table = @Table

    OPEN table_cursor;

    -- Perform the first fetch.
    FETCH NEXT FROM table_cursor INTO @RelatedSchema, @RelatedTable;

    -- Check @@FETCH_STATUS to see if there are any more rows to fetch.
    WHILE @@FETCH_STATUS = 0
    BEGIN

        -- WWB: If We have Recurred To Where We Start This
        -- Is a Circular Reference
        -- Begin failing out of the recursions
        IF (@BaseSchmea = @RelatedSchema AND 
                @BaseTable = @RelatedTable)
            BEGIN
                SET @Fail = @RelatedSchema + '.' + @RelatedTable
                RETURN
            END
        ELSE            
        BEGIN

            DECLARE @Count int

            -- WWB: Check to make sure that the dependencies are not in the stack
            -- If they are we don't need to go down this branch
            SELECT    @Count = COUNT(1)
            FROM    #Stack    
            WHERE    #Stack.[Schema] = @RelatedSchema AND 
                #Stack.[Table] = @RelatedTable

            IF (@Count=0) 
            BEGIN
                -- WWB: Recurse
                EXECUTE SqlAzureRecursiveFind @BaseSchmea, 
                    @BaseTable, 
                    @RelatedSchema, @RelatedTable, @Fail OUTPUT
                IF (LEN(@Fail) > 0)
                BEGIN
                    -- WWB: If the Call Fails, Build the Output Up
                    SET @Fail = @RelatedSchema + '.' + @RelatedTable 
                        + ' -> ' + @Fail
                    RETURN
                END
            END
       END

       -- This is executed as long as the previous fetch succeeds.
    FETCH NEXT FROM table_cursor INTO @RelatedSchema, @RelatedTable;
    END

    CLOSE table_cursor;
    DEALLOCATE table_cursor;    

GO    

SET NOCOUNT ON

DECLARE @Schema nvarchar(max)
DECLARE @Table nvarchar(max)
DECLARE @Fail nvarchar(max)

-- WWB: Loop Through All the Tables In the Database Checking Each One
DECLARE list_cursor CURSOR FOR
      SELECT [Schema], [Table]
      FROM #TableList

OPEN list_cursor;

-- Perform the first fetch.
FETCH NEXT FROM list_cursor INTO @Schema, @Table;

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN

    -- WWB: Clear the Stack (Don't you love Global Variables)
    DELETE #Stack

    -- WWB: Initialize the Input
    SET @Fail = ''

    -- WWB: Check the Table
    EXECUTE SqlAzureRecursiveFind @Schema, 
        @Table, @Schema,
         @Table, @Fail OUTPUT
    IF (LEN(@Fail) > 0)
    BEGIN
        -- WWB: Failed, Output
        SET @Fail = @Schema + '.' + @Table + ' -> ' + @Fail
        PRINT @Fail
    END

   -- This is executed as long as the previous fetch succeeds.
    FETCH NEXT FROM list_cursor INTO @Schema, @Table;
END

-- WWB: Clean Up
CLOSE list_cursor;
DEALLOCATE list_cursor;    

DROP TABLE #TableRelationships
DROP TABLE #Stack
DROP TABLE #TableList
DROP PROC SqlAzureRecursiveFind