List Tables in Dependency (Foreign Key) Order - SQL Server

This page is part of the Foreign Key Pages collection.
Click the icon to see the index.


See Also


Inserting and Deleting Data

When copying data between different instances of the same database, you have to be sure to do it in the right order, or else you'll end up violating a foreign key. You have a similar situation if you're trying to clear out all the data from a database -- you have to do it in the right order or you'll violate a foreign key constraint.

Based on the foreign keys in the database, the following SQL generates a list of DELETE statements in the order they should be to avoid any foreign key constraints. To copy data from one instance of a database to another, build an SSIS (SQL Server Integration Service) package to copy the data in the reverse order of how the tables are listed here.

{copytext|div1}
with Fkeys as (

    select distinct

         OnTable       = OnTable.name
        ,AgainstTable  = AgainstTable.name 

    from 

        sysforeignkeys fk

        inner join sysobjects onTable 
            on fk.fkeyid = onTable.id

        inner join sysobjects againstTable  
            on fk.rkeyid = againstTable.id

    where 1=1
        AND AgainstTable.TYPE = 'U'
        AND OnTable.TYPE = 'U'
        -- ignore self joins; they cause an infinite recursion
        and OnTable.Name <> AgainstTable.Name
    )

,MyData as (

    select 
         OnTable = o.name
        ,AgainstTable = FKeys.againstTable

    from 

        sys.objects o

        left join FKeys
            on  o.name = FKeys.onTable

    where 1=1
        and o.type = 'U'
        and o.name not like 'sys%'
    )

,MyRecursion as (

    -- base case
    select 
         TableName    = OnTable
        ,Lvl        = 1
    from
        MyData
    where 1=1
        and AgainstTable is null

    -- recursive case
    union all select
         TableName    = OnTable
        ,Lvl        = r.Lvl + 1
    from 
        MyData d
        inner join MyRecursion r
            on d.AgainstTable = r.TableName
)
select 
     Lvl = max(Lvl)
    ,TableName
    ,strSql = 'delete from [' + tablename + ']'
from 
    MyRecursion
group by
    TableName
order by 
     1 desc
    ,2 desc

Deleting Selected Data

Generating Delete Statements

This script deletes an entity (or set of entities) from a specified table. It automatically deletes the dependencies (as defined by foreign keys) in the correct order.

ASSUMPTIONS
  1. No self-referencing foreign keys or circular dependencies. (See below for how to detect these.)
  2. All foreign keys are single-column

declare
     @RootTableExt  nvarchar(1000)
    ,@RootColumn    nvarchar(1000)
    ,@FilterClause  nvarchar(1000)

select
     @RootTableExt  = '[dbo].[Person]'
    ,@RootColumn    = 'PersonID'
    ,@FilterClause  = 'in (1,2)'

;with MyData as (

    select
         ForeignKey         = fk.name
        ,OnTableExt         = convert(nvarchar(1000), '[' + ots.name + '].[' + OnTable.name + ']')
        ,OnColumn           = OnColumn.name
        ,AgainstTableExt    = convert(nvarchar(1000), '[' + ats.name + '].[' + AgainstTable.name + ']')
        ,AgainstColumn      = AgainstColumn.name

    from 

        sys.foreign_keys fk

        inner join sys.foreign_key_columns fkcols
            on fk.object_id = fkcols.constraint_object_id

        inner join sys.objects OnTable
            on fk.parent_object_id = OnTable.object_id

        inner join sys.schemas ots
            on OnTable.schema_id = ots.schema_id

        inner join sys.objects AgainstTable
            on fk.referenced_object_id = AgainstTable.object_id

        inner join sys.schemas ats
            on AgainstTable.schema_id = ats.schema_id

        inner join sys.columns OnColumn
            on fkcols.parent_column_id = OnColumn.column_id
            and fkcols.parent_object_id = OnColumn.object_id

        inner join sys.columns AgainstColumn
            on fkcols.referenced_column_id = AgainstColumn.column_id
            and fkcols.referenced_object_id = AgainstColumn.object_id
            
    where 1=1
        AND AgainstTable.TYPE = 'U'
        AND OnTable.TYPE = 'U'
        and OnTable.name not like 'sys%'
        -- ignore self joins; they cause infinite recursion
        and OnTable.Name <> AgainstTable.Name
    )

,MyRecursion as (

    -- base case
    select 
         TableName      = OnTableExt
        ,Lvl            = 1
        ,WhereClause    = CONVERT(nvarchar(4000), ' where [' + @RootColumn + '] ' + @FilterClause)
        ,ParentTable    = CONVERT(nvarchar(1000), null)
    from
        MyData
    where 1=1
        and OnTableExt = @RootTableExt

    -- recursive case
    union all select
         TableName      = OnTableExt
        ,Lvl            = r.Lvl + 1
        ,WhereClause    = CONVERT(nvarchar(4000), 
                            case r.Lvl when 1 then
                                ' where [' + OnColumn + '] ' + @FilterClause
                            else
                                ' where [' + OnColumn + '] in (select [' + AgainstColumn + '] from ' + AgainstTableExt + ' ' + r.WhereClause + ')'
                            end
                            )
        ,ParentTable    = AgainstTableExt
    from 
        MyData d
        inner join MyRecursion r
            on d.AgainstTableExt = r.TableName
)
,MySql as (
    select 
         Lvl            = max(Lvl)
        ,Sort2          = 30
        ,TableName
        ,ParentTable
        ,WhereClause
        ,SqlCode        = 'delete from ' + tablename + WhereClause
    from 
        MyRecursion
    group by
         TableName
        ,WhereClause
        ,ParentTable
        ,WhereClause
    )
select
     Sort0 = 10
    ,Lvl = 0
    ,Sort2 = 0
    ,TableName = ''
    ,SqlCode = '/*================================================================================================='
union select
     Sort0 = 11
    ,Lvl = 0
    ,Sort2 = 0
    ,TableName = ''
    ,SqlCode = '    USAGE NOTE: Uncomment DELETE statements as needed to address errors.                           '
union select
     Sort0 = 12
    ,Lvl = 0
    ,Sort2 = 0
    ,TableName = ''
    ,SqlCode = '=================================================================================================*/'
union select
     Sort0 = 13
    ,Lvl = 0
    ,Sort2 = 0
    ,TableName = ''
    ,SqlCode = 'begin transaction'
union select distinct
     Sort0 = 20
    ,Lvl
    ,Sort2 = 10
    ,TableName = ''
    ,SqlCode = ''
from
    MySql
union select distinct
     Sort0 = 20
    ,Lvl
    ,Sort2 = 20
    ,TableName = ''
    ,SqlCode = '/*--- Level ' + CONVERT(varchar(10), Lvl) + ' ---*/'
from
    MySql
union select
     Sort0 = 20
    ,Lvl
    ,Sort2
    ,TableName
    ,SqlCode = case when Lvl >= 2 then '--' else '' end + SqlCode
from
    MySql
union select
     Sort0 = 30
    ,Lvl = 0
    ,Sort2 = 0
    ,TableName = ''
    ,SqlCode = ''
union select
     Sort0 = 31
    ,Lvl = 0
    ,Sort2 = 0
    ,TableName = ''
    ,SqlCode = 'rollback transaction'
order by 
     Sort0
    ,Lvl desc
    ,Sort2
    ,TableName desc

Viewing Data Related to Selected Entities

declare
     @RootTableExt  nvarchar(1000)
    ,@RootColumn    nvarchar(1000)
    ,@FilterClause  nvarchar(1000)

select
     @RootTableExt  = '[dbo].[Condition]'
    ,@RootColumn    = 'ConditionID'
    ,@FilterClause  = '= -1'

;with MyData as (

    select
         ForeignKey         = fk.name
        ,OnTableExt         = convert(nvarchar(1000), '[' + ots.name + '].[' + OnTable.name + ']')
        ,OnColumn           = OnColumn.name
        ,AgainstTableExt    = convert(nvarchar(1000), '[' + ats.name + '].[' + AgainstTable.name + ']')
        ,AgainstColumn      = AgainstColumn.name

    from 

        sys.foreign_keys fk

        inner join sys.foreign_key_columns fkcols
            on fk.object_id = fkcols.constraint_object_id

        inner join sys.objects OnTable
            on fk.parent_object_id = OnTable.object_id

        inner join sys.schemas ots
            on OnTable.schema_id = ots.schema_id

        inner join sys.objects AgainstTable
            on fk.referenced_object_id = AgainstTable.object_id

        inner join sys.schemas ats
            on AgainstTable.schema_id = ats.schema_id

        inner join sys.columns OnColumn
            on fkcols.parent_column_id = OnColumn.column_id
            and fkcols.parent_object_id = OnColumn.object_id

        inner join sys.columns AgainstColumn
            on fkcols.referenced_column_id = AgainstColumn.column_id
            and fkcols.referenced_object_id = AgainstColumn.object_id
            
    where 1=1
        AND AgainstTable.TYPE = 'U'
        AND OnTable.TYPE = 'U'
        and OnTable.name not like 'sys%'
        -- ignore self joins; they cause infinite recursion
        and OnTable.Name <> AgainstTable.Name
    )

,MyRecursion as (

    -- base case
    select 
         TableName      = OnTableExt
        ,Lvl            = 1
        ,WhereClause    = CONVERT(nvarchar(4000), ' where [' + @RootColumn + '] ' + @FilterClause)
        ,ParentTable    = CONVERT(nvarchar(1000), null)
    from
        MyData
    where 1=1
        and OnTableExt = @RootTableExt

    -- recursive case
    union all select
         TableName      = OnTableExt
        ,Lvl            = r.Lvl + 1
        ,WhereClause    = CONVERT(nvarchar(4000), 
                            case r.Lvl when 1 then
                                ' where [' + OnColumn + '] ' + @FilterClause
                            else
                                ' where [' + OnColumn + '] in (select [' + AgainstColumn + '] from ' + AgainstTableExt + ' ' + r.WhereClause + ')'
                            end
                            )
        ,ParentTable    = AgainstTableExt
    from 
        MyData d
        inner join MyRecursion r
            on d.AgainstTableExt = r.TableName
)
,MySql as (
    select 
         Lvl            = max(Lvl)
        ,Sort2          = 30
        ,TableName
        ,ParentTable
        ,WhereClause
        ,SqlCode        = 'select TableName = ''' + TableName + ''', Qty = count(2) from ' + tablename + WhereClause
    from 
        MyRecursion
    group by
         TableName
        ,WhereClause
        ,ParentTable
        ,WhereClause
    )
select
     Sort0 = 20
    ,Lvl
    ,Sort2
    ,TableName
    ,SqlCode = case when Lvl > 1 then 'union ' else '' end + SqlCode
from
    MySql
union select
     Sort0 = 31
    ,Lvl = 0
    ,Sort2 = 0
    ,TableName = ''
    ,SqlCode = 'order by TableName'
order by 
     Sort0
    ,Lvl

Detecting Circular Dependencies

The above code will not work if foreign key relationships create a chain of circular dependencies. The following SQL statement will list all such circular dependencies, up to 5 levels (tables) deep. In graph theory, this is called "cycle detection", although a general algorithm would detect all such cycles regardless of how many nodes they contain.

with fk as (
    select
         OnTable            = '[' + ots.name + '].[' + OnTable.name + ']'
        ,AgainstTable       = '[' + ats.name + '].[' + AgainstTable.name + ']'

    from 

        sys.foreign_keys fk

        inner join sys.objects OnTable
            on fk.parent_object_id = OnTable.object_id

        inner join sys.schemas ots
            on OnTable.schema_id = ots.schema_id

        inner join sys.objects AgainstTable
            on fk.referenced_object_id = AgainstTable.object_id

        inner join sys.schemas ats
            on AgainstTable.schema_id = ats.schema_id
)
/*--- 1 Table ---*/
select
     NodeQty = 1
    ,NodePath = a1.OnTable + '>' + a1.AgainstTable
from
    fk a1
where a1.OnTable = a1.AgainstTable
/*--- 2 Tables ---*/
union
select
     NodeQty = 2
    ,NodePath = a1.OnTable + '>' + a2.OnTable + '>' + a2.AgainstTable
from
    fk a1
    left join fk a2 on a1.AgainstTable = a2.OnTable
where 1=1
    and a1.OnTable <> a1.AgainstTable
    and a1.OnTable = a2.AgainstTable
/*--- 3 Tables ---*/
union
select
     NodeQty = 3
    ,NodePath = a1.OnTable + '>' + a2.OnTable + '>' + a3.OnTable + '>' + a3.AgainstTable
from
    fk a1
    left join fk a2 on a1.AgainstTable = a2.OnTable
    left join fk a3 on a2.AgainstTable = a3.OnTable
where 1=1
    and a1.OnTable <> a1.AgainstTable
    and a1.OnTable <> a2.AgainstTable
    and a1.OnTable =  a3.AgainstTable

/*--- 4 Tables ---*/
union
select
     NodeQty = 4
    ,NodePath = a1.OnTable + '>' + a2.OnTable + '>' + a3.OnTable + '>' + a4.OnTable + '>' + a4.AgainstTable
from
    fk a1
    left join fk a2 on a1.AgainstTable = a2.OnTable
    left join fk a3 on a2.AgainstTable = a3.OnTable
    left join fk a4 on a3.AgainstTable = a4.OnTable
where 1=1
    and a1.OnTable <> a1.AgainstTable
    and a1.OnTable <> a2.AgainstTable
    and a1.OnTable <> a3.AgainstTable
    and a1.OnTable =  a4.AgainstTable

/*--- 5 Tables ---*/
union
select
     NodeQty = 5
    ,NodePath = a1.OnTable + '>' + a2.OnTable + '>' + a3.OnTable + '>' + a4.OnTable + '>' + a4.AgainstTable
from
    fk a1
    left join fk a2 on a1.AgainstTable = a2.OnTable
    left join fk a3 on a2.AgainstTable = a3.OnTable
    left join fk a4 on a3.AgainstTable = a4.OnTable
    left join fk a5 on a4.AgainstTable = a5.OnTable
where 1=1
    and a1.OnTable <> a1.AgainstTable
    and a1.OnTable <> a2.AgainstTable
    and a1.OnTable <> a3.AgainstTable
    and a1.OnTable <> a4.AgainstTable
    and a1.OnTable =  a5.AgainstTable

order by NodeQty, NodePath

Viewing the Hierarchy

When you are first becoming familiar with a database, sometimes it helps to view the foreign key dependencies in a hierarchy. The following SQL statements will create just such a hierarchical tree — one "top-down", the other "bottom-up".

Top Down Hierarchy

The following SQL produces records that look like the following sample.

LevelTableNameDepPath
..1OrderHeaderOrderHeader
....2OrderLineOrderHeader\OrderLine
......3PartMasterOrderHeader\OrderLine\PartMaster

{copytext|topdown}
with Fkeys as (

    select distinct

         OnTable       = OnTable.name
        ,AgainstTable  = AgainstTable.name 

    from 

        sysforeignkeys fk

        inner join sysobjects onTable 
            on fk.fkeyid = onTable.id

        inner join sysobjects againstTable  
            on fk.rkeyid = againstTable.id

    where 1=1
        AND AgainstTable.TYPE = 'U'
        AND OnTable.TYPE = 'U'
        -- ignore self joins; they cause an infinite recursion
        and OnTable.Name <> AgainstTable.Name
    )

,MyData as (

    select 
         OnTable = o.name
        ,AgainstTable = FKeys.againstTable

    from 

        sys.objects o

        left join FKeys
            on  o.name = FKeys.onTable

    where 1=1
        and o.type = 'U'
        and o.name not like 'sys%'
    )

,MyRecursion as (

    -- base case
    select 
         TableName    = OnTable
        ,Lvl        = 1
        ,DepPath    = convert(varchar(max), OnTable)
    from
        MyData
    where 1=1
        and AgainstTable is null

    -- recursive case
    union all select
         TableName    = OnTable
        ,Lvl        = r.Lvl + 1
        ,DepPath    = convert(varchar(max), r.DepPath + '\' + OnTable)
    from 
        MyData d
        inner join MyRecursion r
            on d.AgainstTable = r.TableName
)
select 
     Level = replicate('..', Lvl) + convert(varchar(3), Lvl)
    ,TableName
    ,DepPath
from 
    MyRecursion
order by 
     DepPath

Bottom-Up Hierarchy

The following SQL produces records that look like the following sample.

LevelTableNameDepPath
..1PartMasterPartMaster
....2OrderLinePartMaster\OrderLine
......3OrderHeaderPartMaster\OrderLine\OrderHeader

{copytext|bottomup}
with Fkeys as (

    select distinct

         OnTable       = OnTable.name
        ,AgainstTable  = AgainstTable.name 

    from 

        sysforeignkeys fk

        inner join sysobjects onTable 
            on fk.fkeyid = onTable.id

        inner join sysobjects againstTable  
            on fk.rkeyid = againstTable.id

    where 1=1
        AND AgainstTable.TYPE = 'U'
        AND OnTable.TYPE = 'U'
        -- ignore self joins; they cause an infinite recursion
        and OnTable.Name <> AgainstTable.Name
    )

,MyData as (

    select 
         OnTable		= FKeys.onTable
        ,AgainstTable	= o.name

    from 

        sys.objects o

        left join FKeys
            on o.name = FKeys.againstTable

    where 1=1
        and o.type = 'U'
        and o.name not like 'sys%'
    )

,MyRecursion as (

    -- base case
    select 
         TableName  = AgainstTable
        ,Lvl        = 1
        ,DepPath    = convert(varchar(max), AgainstTable)
    from
        MyData
    where 1=1
        and OnTable is null

    -- recursive case
    union all select
         TableName  = AgainstTable
        ,Lvl        = r.Lvl + 1
        ,DepPath    = convert(varchar(max), r.DepPath + '\' + AgainstTable)
    from 
        MyData d
        inner join MyRecursion r
            on d.OnTable = r.TableName
)
select 
     Level = replicate('..', Lvl) + convert(varchar(3), Lvl)
    ,TableName
    ,DepPath
from 
    MyRecursion
order by 
     DepPath