SQL Server Mentalist

Hi Folks,

In this article we are going to cover How to Find Circular reference in given Database.

In this post we are going to discuss following points:

• Problem Statement of SQL Script:
• Description of SQL Script:
• SQL Script Code
• SQL Script Output Screenshot
• User Level to execute

Problem Statement of SQL Script:

Find Circular reference?

Description of SQL Script:

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.

What is a Circular Reference?

Foreign keys create database-enforced integrity constraints. These constraints ensure that a row of data exists in one table before another table can reference it. They also prevent a dependent row from being deleted that another row references. In Figure 1 we see a simple foreign key between Address table and StateProvince table in the Adventure Works database.

Figure 1

A circular reference is one or more tables where the foreign keys create a loop. Figure 2 is an example.

Figure 2

In this case the City table contains a reference to the author; it is the author that wrote the description for the city. The Author table has a reference to the city, because each author lives in a city. So which came first, the city or the author? In all cases with circular references one of the foreign key columns must be accept a null value. This allows the data to be inserted in 3 passes:

An insert into the table referenced by the nullable foreign key with the key set to null.

An insert into the table with the non-null foreign key.

An update to modify the nullable foreign key to reference the row inserted in step 2.

A circular reference is not limited to two tables, it might involve many tables, all bound together in one big circle.

Self-Referencing Tables

A special case circular reference is the self-referencing table. This is a table that has a foreign key column that references its own primary key. An example is a human resource schema that tracks employees and their bosses. In the employee table, there is a foreign key column called boss that references the primary key column in the employee table. Self-referencing tables always have a foreign key column which is nullable and at least one null exists. In the example above it would be the CEO, since he doesn’t have a boss his boss column is null.

Synchronizing Schemas with Circular References

Tables that are not involved in a circular reference are easy to synchronize, you make a complete table update the table without dependencies on it, then update the tables with foreign key dependences. In Figure 1 you would update the StateProvince table, then the Address table. This explanation is simplified, for example the deletes are done in the reverse order. If the tables have no circular references you can synchronize them table by table if you know their dependency order.

Synchronizing tables with circular references is much harder, because you have to update the tables row by row, jumping back and forth between the tables, inserting the nullable foreign key with nulls first, then updating them later. Again this is a simplified explanation; the point is that you can’t update the tables in a serial order if there are circular references.

There are really only a couple ways to synchronize database that contains tables with circular references:

Perform a transaction based replication, much like SQL Server replication, which updates, inserts, and deletes the data in the same serial order as the data was changed in the source database

Set the database into read-only mode, bulk copy the rows over to the destination database with the same primary keys, without check constraints on. Once you have moved all the tables, the source database can be taken out of read-only mode. I blog about doing this with bcp utility here.

Deduce the possible orders of inserts, updates, and deletes row by row based on the dependencies and recreate those on the destination database. This is comparable to backwards engineering the transactions it took to update, insert and delete the data.

Detecting Circular References

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.

SQL Script Code

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

User Level to execute

300

Hope you will like How to Find Circular reference in given Database.

If you really like reading my blog and understood at least few thing then please don’t forget to subscribe my blog.

If you want daily link and analysis or interesting link go to following website which will give @ your inbox please subscribe our following link resource blog :

Link Resource Website

For More information related to BI World visit my Mentalist Blog

SQL Server Mentalist >> SQL Learning Blog

Business Intelligence Mentalist >> BI World

Infographic Mentalist >> Image worth explaining thousand Words

Microsoft Mentalist >> MVC,ASP.NET, WCF & LinQ

DBA Mentalist >>Advance SQL Server Blog

Microsoft BI Mentalist >> MS BI Development Update

Connect With me on

| FaceBook |Twitter | linkedIn| Google+ | WordPress | RSS |