When working with a complex SQL Server database, you may often need to know the order of tables based on foreign key dependencies.
For example:
-
When generating scripts for data migration.
-
When planning truncation or deletion of data.
-
When seeding tables in the correct sequence.
Manually figuring out the dependency tree can be painful, especially in large schemas. Luckily, we can solve this with a recursive Common Table Expression (CTE).
Here’s a query that produces all tables in order of their dependency level (starting from tables without foreign key dependencies):
WITH TableDependencies AS (
-- Anchor member: Tables with no outgoing foreign key dependencies
SELECT
t.object_id AS TableId,
SCHEMA_NAME(t.schema_id) AS SchemaName,
t.name AS TableName,
0 AS Level,
CAST(t.object_id AS VARCHAR(MAX)) AS Path -- Track visited tables
FROM sys.tables AS t
LEFT JOIN sys.foreign_keys AS fk
ON t.object_id = fk.parent_object_id
WHERE fk.parent_object_id IS NULL
UNION ALL
-- Recursive member: Child tables in foreign key relationships
SELECT
t.object_id AS TableId,
SCHEMA_NAME(t.schema_id) AS SchemaName,
t.name AS TableName,
td.Level + 1 AS Level,
td.Path + '>' + CAST(t.object_id AS VARCHAR(MAX)) AS Path
FROM sys.tables AS t
INNER JOIN sys.foreign_keys AS fk
ON t.object_id = fk.parent_object_id
INNER JOIN TableDependencies AS td
ON fk.referenced_object_id = td.TableId
WHERE td.Path NOT LIKE '%'+CAST(t.object_id AS VARCHAR(MAX))+'%' -- Prevent cycles
)
SELECT
Max(Level) as Level,
SchemaName,
TableName
FROM TableDependencies
Group BY SchemaName,
TableName
order by
Level,
SchemaName,
OPTION (MAXRECURSION 32767); -- Increase recursion depth if needed
How It Works
-
Anchor query: Finds tables with no outgoing dependencies (no foreign keys referencing other tables).
-
Recursive query: Walks through foreign key relationships to find child tables, increasing the level.
-
Path column: Ensures we don’t loop infinitely in case of circular references.
-
MAXRECURSION: Allows deep dependency chains (up to 32,767 levels).
