Deleting Data in Foreign Key Order - SQL Server

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

Overview

Based on the foreign keys in the database, the following SQL generates a list of DELETE statements in the order they should be executed to avoid any issues with foreign key constraints.

Code

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

select
     @RootTable     = 'Tenant'
    ,@RootColumn    = 'Id'
    ,@FilterClause  = 'in (10)'

;with MyRecursion as (

    /*--- Base Case ---*/
    SELECT
         TableName      = [name]
        ,Lvl            = 1
        ,WhereClause    = convert(nvarchar(4000), ' where [' + @RootColumn + '] ' + @FilterClause)
        ,ParentTable    = convert(nvarchar(1000), null)
        ,DepPath        = convert(varchar(8000), '/' + [name])
    FROM    
        sys.tables
    where 1=1
        and [name] = @RootTable

    /*--- Recursive Case ---*/
    union all SELECT
         TableName      = f.OnTable
        ,Lvl            = r.Lvl + 1
        ,WhereClause    = convert(nvarchar(4000),
                            case r.Lvl when 1 THEN
                                ' where ['
                                    + f.OnColumn
                                    + '] '
                                    + @FilterClause
                            else
                                ' where [' 
                                    + f.OnColumn 
                                    + '] in (select [' 
                                    + f.AgainstColumn + '] from [' 
                                    + f.AgainstTable 
                                    + '] ' 
                                    + r.WhereClause 
                                    + ')'
                            end
                            )
        ,ParentTable    = convert(nvarchar(1000), f.AgainstTable)
        ,DepPath        = convert(varchar(8000), r.DepPath + '/' + f.OnTable )
    FROM    
        dbo.FKeys f
        inner join MyRecursion r
            on f.AgainstTable = r.TableName

            /* TODO: Exclude entities we want to keep */
            and f.OnTable not in ('AspNetUsers','AspNetRoles')
            
            /* Avoid circular references */
            and r.DepPath not like '%/' + f.OnTable + '/%'

)
,DeleteStatements as (
    SELECT
         Lvl        = max(Lvl)
        ,TableName
        ,SqlCode    = 'delete from [' + TableName + ']' + WhereClause
    FROM    
        MyRecursion
    WHERE 1=1
        and TableName not in ('Lab')
    group BY    
        TableName
        ,WhereClause
    )
select
    *
    --distinct TableName, Lvl
FROM
    DeleteStatements
order by 
     Lvl desc
    ,TableName