Get SQL Server Tables in Dependency Order Using Recursive CTE

 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).