databasesql-server-2008stored-procedurescommon-table-expression

How to find circular dependent table in sql server

Currently im working on finding the dependency 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??

Solution

  • 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;