How to find circular dependent table in sql server
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 fk1INNER JOIN sys.foreign_keys AS fk2ON fk1.parent_object_id = fk2.referenced_object_idAND fk2.parent_object_id = fk1.referenced_object_id;
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 PerformanceCREATE 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 CheckCREATE TABLE #TableList ([Schema] nvarchar(max), [Table] nvarchar(max))-- WWB: Fill the Table ListINSERT INTO #TableList ([Table], [Schema])SELECT TABLE_NAME, TABLE_SCHEMAFROM INFORMATION_SCHEMA.TABLES WHERE Table_Type = 'BASE TABLE'-- WWB: Fill the RelationShip Temp TableINSERT INTO #TableRelationships(FK_Schema, FK_Table, PK_Schema, PK_Table)SELECT FK.TABLE_SCHEMA, FK.TABLE_NAME, PK.TABLE_SCHEMA, PK.TABLE_NAMEFROM 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_NAMECREATE TABLE #Stack([Schema] nvarchar(max), [Table] nvarchar(max))GO-- WWB: Drop SqlAzureRecursiveFindIF 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 ItselfCREATE PROC SqlAzureRecursiveFind @BaseSchmea nvarchar(max), @BaseTable nvarchar(max), @Schmea nvarchar(max), @Table nvarchar(max), @Fail nvarchar(max) OUTPUTAS 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 ONDECLARE @Schema nvarchar(max)DECLARE @Table nvarchar(max)DECLARE @Fail nvarchar(max)-- WWB: Loop Through All the Tables In the Database Checking Each OneDECLARE list_cursor CURSOR FOR SELECT [Schema], [Table] FROM #TableListOPEN 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 = 0BEGIN -- 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 UpCLOSE list_cursor;DEALLOCATE list_cursor; DROP TABLE #TableRelationshipsDROP TABLE #StackDROP TABLE #TableListDROP PROC SqlAzureRecursiveFind
Download a free trial of Sql Dependency Tracker and give it a spin. If it works for you, buy it :-)
http://www.red-gate.com/products/sql-development/sql-dependency-tracker/
Another option is ApexSQL:
http://knowledgebase.apexsql.com/2012/08/apexsql-search-dependency-viewer.html
Both are good tools.