Page History: Deleting Data in Foreign Key Order - SQL Server

Compare Page Revisions

0 Current 0

« Older Revision - Back to Page History - Current Revision

Page Revision: Tue, Jan 11, 2022, 12:33 PM

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