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??
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;
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
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.
The script from SQL Azure Team Blog in answer 1 and it only shows self-references - parent/child relations to the same table. So I wrote my own script:
-- variables for the path output
declare @delimList nvarchar(max) = ' > ',
@delimDot nvarchar(max) = '.'
/* Part 1: read all fk-pk relation
does not perform well in SQL Server with a CTE, thus using a temp table */
create table #fk_pk(
PK_schema sysname not null,
PK_table sysname not null,
FK_schema sysname not null,
FK_table sysname not null
)
insert into #fk_pk(
PK_schema,
PK_table,
FK_schema,
FK_table
)
select distinct
PK.TABLE_SCHEMA PK_schema,
PK.TABLE_NAME PK_table,
FK.TABLE_SCHEMA FK_schema,
FK.TABLE_NAME FK_table
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK
inner join
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
on C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
inner join
INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
on C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
where PK.CONSTRAINT_TYPE = 'PRIMARY KEY'
and
-- ignore self-references
not (
PK.TABLE_SCHEMA = FK.TABLE_SCHEMA
and
PK.TABLE_NAME = FK.TABLE_NAME
)
;
with relation(
sourceSchema,
sourceTable,
PK_schema,
PK_table,
FK_schema,
FK_table,
path
) as (
/* Part 2: Find PKs that are referenced more then once (reduces workload for next step) */
-- anchor: more then one fk reference these pk tables
select fk_pk.PK_schema sourceSchema,
fk_pk.PK_table sourceTable,
fk_pk.PK_schema,
fk_pk.PK_table,
fk_pk.FK_schema,
fk_pk.FK_table,
cast(fk_pk.PK_schema as nvarchar(max)) + @delimDot + fk_pk.PK_table + @delimList + fk_pk.FK_schema + @delimDot + fk_pk.FK_table path
from #fk_pk fk_pk
where exists(
select 1
from #fk_pk fk_pk_exists
where fk_pk_exists.PK_schema = fk_pk.PK_schema
and
fk_pk_exists.PK_table = fk_pk.PK_table
and
not (
fk_pk_exists.FK_schema = fk_pk.FK_schema
and
fk_pk_exists.FK_table = fk_pk.FK_table
)
)
/* Part 3: Find all possible paths from those PK tables to any other table (using recursive CTE) */
union all
-- recursive
select relation.sourceSchema,
relation.sourceTable,
fk_pk_child.PK_schema,
fk_pk_child.PK_table,
fk_pk_child.FK_schema,
fk_pk_child.FK_table,
/* Part 5: Display result nicely
compose a path like: A -> B -> C */
relation.path + @delimList + fk_pk_child.FK_schema + @delimDot + fk_pk_child.FK_table path
from #fk_pk fk_pk_child
inner join
relation
on relation.FK_schema = fk_pk_child.PK_schema
and
relation.FK_table = fk_pk_child.PK_table
)
/* Part 4: Identify problematic circles */
select relation.sourceSchema + @delimDot + relation.sourceTable source,
relation.FK_schema + @delimDot + relation.FK_table target,
relation.path
from relation
where exists(
select 1
from relation relation_exists
where relation_exists.sourceSchema = relation.sourceSchema
and
relation_exists.sourceTable = relation.sourceTable
and
not (
relation_exists.PK_schema = relation.PK_schema
and
relation_exists.PK_table = relation.PK_table
)
and
relation_exists.FK_schema = relation.FK_schema
and
relation_exists.FK_table = relation.FK_table
)
order by relation.sourceSchema,
relation.sourceTable,
relation.FK_schema,
relation.FK_table,
relation.path
drop table #fk_pk
go
It reports circular references and the paths that form the circle.
The script is for SQL Server only, hosted at github repo with code explanation. Please let me know if you port it to other RDBMS.
