Script to Delete Data from SQL Server Tables with Foreign Key Constraints

By:   |   Comments (24)   |   Related: > Constraints


Problem

Let's say we have a SQL Server table named Table1 and it is referenced by multiple tables via foreign keys (FKs) and these multiple tables again are referenced by other tables via FKs. If I want to delete some data or all data from Table1 and the FKs are not configured as cascading constraints on delete, then if I need to delete from Table1 I have to delete from the leaf level tables first which can get tricky to figure out.

Solution

There are generally two scenarios when deleting data from tables with FKs, one is to delete all of the data and the other is to delete a few records. Of course, for both scenarios we need to delete from the tables in the right order. The first scenario is a little simpler as we just need to delete all rows from the table, but the second scenario is more challenging as we need to delete rows with a WHERE clause combined with complex joins and/or subqueries.

There are two challenges that we will need to deal with:

  • We may have complex and long foreign key relationships
  • Foreign keys may be composed of multiple columns

This solution will first rely on recursion to search the whole "foreign key tree" starting from the root table, and log the information to a table, then we will loop through this table starting with the leaf level tables, and find the corresponding FKs and then compose the delete statement.

Create Stored Procedure to Display Foreign Key Tree

First we compose a stored procedure to "scan the FK tree" via recursion, here is the code:

use MSSQLTips -- change to your own db

if object_id('dbo.usp_searchFK', 'P') is not null
	drop proc dbo.usp_SearchFK;
go
create proc dbo.usp_SearchFK 
  @table varchar(256) -- use two part name convention
, @lvl int=0 -- do not change
, @ParentTable varchar(256)='' -- do not change
, @debug bit = 1
as
begin
	set nocount on;
	declare @dbg bit;
	set @dbg=@debug;
	if object_id('tempdb..#tbl', 'U') is null
		create table  #tbl  (id int identity, tablename varchar(256), lvl int, ParentTable varchar(256));
	declare @curS cursor;
	if @lvl = 0
		insert into #tbl (tablename, lvl, ParentTable)
		select @table, @lvl, Null;
	else
		insert into #tbl (tablename, lvl, ParentTable)
		select @table, @lvl,@ParentTable;
	if @dbg=1	
		print replicate('----', @lvl) + 'lvl ' + cast(@lvl as varchar(10)) + ' = ' + @table;
	
	if not exists (select * from sys.foreign_keys where referenced_object_id = object_id(@table))
		return;
	else
	begin -- else
		set @ParentTable = @table;
		set @curS = cursor for
		select tablename=object_schema_name(parent_object_id)+'.'+object_name(parent_object_id)
		from sys.foreign_keys 
		where referenced_object_id = object_id(@table)
		and parent_object_id <> referenced_object_id; -- add this to prevent self-referencing which can create a indefinitive loop;

		open @curS;
		fetch next from @curS into @table;

		while @@fetch_status = 0
		begin --while
			set @lvl = @lvl+1;
			-- recursive call
			exec dbo.usp_SearchFK @table, @lvl, @ParentTable, @dbg;
			set @lvl = @lvl-1;
			fetch next from @curS into @table;
		end --while
		close @curS;
		deallocate @curS;
	end -- else
	if @lvl = 0
		select * from #tbl;
	return;
end
go

Create Sample Tables and Data

The following T-SQL creates a few tables with FKs. Some of the FKs contain two columns and some tables have multiple FKs.

Open a new SSMS window, and run the following code. 

use MSSQLTips -- change to your proper db
GO

drop table dbo.Q1, dbo.Q2, dbo.Q3, dbo.P1, dbo.P2, dbo.P3, dbo.N1, dbo.N2, dbo.M;
GO

-- sample tables with FKs
create table dbo.M (id int primary key, M_c1 int not null, M_c2 datetime not null, M_c3 char(10) not null, M_c4 datetime);
create unique index ux1_A on dbo.M (M_c1);
create unique index ux2_A on dbo.M (M_c2, M_c3);

create table dbo.N1 (id int primary key, N1_c1 int, N1_c2 datetime, N1_c3 char(10), N1_c4 datetime);
create unique index ux1_N1 on dbo.N1 (N1_c1, N1_c3);
create unique index ux2_N1 on dbo.N1 ( N1_c2);
alter table dbo.N1 add constraint FK_N1_M foreign key (N1_c1) references dbo.M (M_c1);

create table dbo.N2 (id int primary key, N2_c1 int, N2_c2 datetime, N2_c3 char(10), N2_c4 datetime);
create unique index ux1_N2 on dbo.N2 (N2_c1);
create unique index ux2_N2 on dbo.N2 (N2_c3, N2_c4);
alter table dbo.N2 add constraint FK1_N2_M foreign key (N2_c2, N2_c3) references dbo.M (M_c2, M_c3);

create table dbo.P1 (id int primary key, P1_c1 int, P1_c2 datetime, P1_c3 char(10), P1_c4 datetime);
create unique index ux1_P1 on dbo.P1 (P1_c1);
alter table dbo.P1 add constraint FK_P1_N1 foreign key (P1_c1, P1_c3) references dbo.N1 (N1_c1, N1_c3);

create table dbo.P2 (id int primary key, P2_c1 int, P2_c2 datetime, P2_c3 char(10), P2_c4 datetime);
create unique index ux1_P2 on dbo.P2 (P2_c1);
create unique index ux2_P2 on dbo.P2 (P2_c2, P2_c3);
alter table dbo.P2 add constraint FK_P2_N1 foreign key (P2_c2) references dbo.N1 (N1_c2);
alter table dbo.P2 add constraint FK_P2_N2 foreign key (P2_c3, P2_c4) references dbo.N2 (N2_c3, N2_c4);

create table dbo.P3 (id int primary key, P3_c1 int, P3_c2 datetime, P3_c3 char(10), P3_c4 datetime);
alter table dbo.P3 add constraint FK_P3_N2 foreign key (P3_c1) references dbo.N2 (id);

create table dbo.Q1 (id int primary key, Q1_c1 int, Q1_c2 datetime, Q1_c3 char(10), Q1_C4 datetime);
alter table dbo.Q1 add constraint FK_Q1_P1 foreign key (Q1_c1) references dbo.P1 (P1_c1);

create table dbo.Q2 (id int primary key, Q2_c1 int, Q2_c2 datetime, Q2_c3 char(10), Q2_c4 datetime);
alter table dbo.Q2 add constraint FK_Q2_P2 foreign key (Q2_c1) references dbo.P2 (id);

create table dbo.Q3 (id int primary key, Q3_c1 int, Q3_c2 datetime, Q3_c3 char(10), Q3_c4 datetime);
alter table dbo.Q3 add constraint FK1_Q3_N2 foreign key (Q3_c1) references dbo.N2 (id);
alter table dbo.Q3 add constraint FK2_Q3_P2 foreign key (Q3_c2, Q3_c3) references dbo.P2 (P2_c2, P2_c3);
GO

-- populate all tables
insert into dbo.M (id, M_c1, M_c2, M_c3, M_c4)
select 1, 10, '2015-01-01', 'AB1', '2015-01-02'
union all
select 2, 20, '2015-01-02', 'AB2', '2015-01-03'
union all
select 3, 30, '2015-01-03', 'AB3', '2015-01-04';

insert into dbo.N1 (id, N1_c1, N1_c2, N1_c3, N1_c4)
select 11, 20, '2015-01-01', 'CD1', '2015-01-02'
union all
select 21, 30, '2015-01-02', 'CD2', '2015-01-03'
union all
select 31, 10, '2015-01-03', 'CD3', '2015-01-04';

insert into dbo.N2 (id, N2_c1, N2_c2, N2_c3, N2_c4)
select 11, 11, '2015-01-01', 'AB1', '2015-01-02'
union all
select 12, 22, '2015-01-02', 'AB2', '2015-01-03'
union all
select 13, 33, '2015-01-03', 'AB3', '2015-01-04';

insert into dbo.P1 (id, P1_c1, P1_c2, P1_c3, P1_c4)
select 100, 20, '2014-01-01', 'CD1', '2015-01-02'
union all
select 101, 30, '2014-01-02', 'CD2', '2015-01-03'
union all
select 102, 10, '2014-01-03', 'CD3', '2015-01-04'

insert into dbo.P2 (id, P2_c1, P2_c2, P2_c3, P2_c4)
select 200, 20, '2015-01-01', 'AB1', '2015-01-02'
union all
select 201, 30, '2015-01-02', 'AB2', '2015-01-03'
union all
select 202, 10, '2015-01-03', 'AB3', '2015-01-04'

insert into dbo.P3 (id, P3_c1, P3_c2, P3_c3, P3_c4)
select 301, 11, '2010-01-01', 'EF1', '2015-02-02'
union all
select 302, 13, '2010-01-02', 'EF2', '2015-02-03'
union all
select 303, 12, '2010-01-03', 'EF3', '2015-02-04'

insert into dbo.Q1 (id, Q1_c1, Q1_c2, Q1_c3, Q1_c4)
select 1001, 10, '1999-10-11', 'GH1', '2015-01-02'
union all
select 1011, 20, '2000-10-12', 'GH2', '2015-01-03'
union all
select 1021, 30, '2001-10-13', 'GH3', '2015-01-04'

insert into dbo.Q2 (id, Q2_c1, Q2_c2, Q2_c3, Q2_c4)
select 2001, 201, '2009-11-11', 'IJ1', '2014-01-12'
union all
select 2011, 202, '2000-12-12', 'IJ2', '2013-01-13'
union all
select 2021, 200, '2010-03-13', 'IJ3', '2012-01-14'

insert into dbo.Q3 (id, Q3_c1, Q3_c2, Q3_c3, Q3_c4)
select 3001, 11, '2015-01-01', 'AB1', '2015-01-02'
union all
select 3011, 12, '2015-01-02', 'AB2', '2015-01-03'
union all
select 3021, 13, '2015-01-03', 'AB3', '2015-01-04';
GO

SELECT [Table]='M', ID, C1=M_c1, C2=M_c2, C3=M_c3, C4=M_c4
FROM DBO.M
union all
SELECT [Table]='N1', ID, C1=N1_c1, C2=N1_c2, C3=N1_c3, C4=N1_c4
FROM DBO.N1
union all
SELECT [Table]='N2', ID, C1=N2_c1, C2=N2_c2, C3=N2_c3, C4=N2_c4
FROM DBO.N2
union all
SELECT [Table]='P1', ID, C1=P1_c1, C2=P1_c2, C3=P1_c3, C4=P1_c4
FROM DBO.P1
union all
SELECT [Table]='P2', ID, C1=P2_c1, C2=P2_c2, C3=P2_c3, C4=P2_c4
FROM DBO.P2
union all
SELECT [Table]='P3', ID, C1=P3_c1, C2=P3_c2, C3=P3_c3, C4=P3_c4
FROM DBO.P3
union all
SELECT [Table]='Q1', ID, C1=Q1_c1, C2=Q1_c2, C3=Q1_c3, C4=Q1_c4
FROM DBO.Q1
union all
SELECT [Table]='Q2', ID, C1=Q2_c1, C2=Q2_c2, C3=Q2_c3, C4=Q2_c4
FROM DBO.Q2
union all
SELECT [Table]='Q3', ID, C1=Q3_c1, C2=Q3_c2, C3=Q3_c3, C4=Q3_c4
FROM DBO.Q3

The diagram below shows the relationships for the tables we just created.

FK table diagram

Display Foreign Key Tree

Let's open another SSMS query window, and run the following code to show the FK tree. We want to use the highest level table, which is the dbo.M table for the sample tables we created.

-- if you just want to see the FK hierarchy, then run this-- and check the result under Message tab
use MSSQLTips
exec dbo.usp_SearchFK 'dbo.M' --two part naming convention

We will see the following result:

FK Hierarchy diagram

Deleting Specific SQL Server Records with Foreign Keys

Let's say I want to delete a record in dbo.M table WHERE id=2. We can open a new SSMS query window and run the following script.

-- the following is an example to show what code can be generated to do the table deletionset nocount on;

if object_id('tempdb..#tmp') is not null
	drop table #tmp;
create table  #tmp  (id int, tablename varchar(256), lvl int, ParentTable varchar(256));

insert into #tmp 
exec dbo.usp_SearchFK @table='dbo.M', @debug=0;

declare @where varchar(max) ='where M.id=2' -- if @where clause is null or empty, it will delete tables as a whole with the right order
declare @curFK cursor, @fk_object_id int;
declare @sqlcmd varchar(max)='', @crlf char(2)=char(0x0d)+char(0x0a);
declare @child varchar(256), @parent varchar(256), @lvl int, @id int;
declare @i int;
declare @t table (tablename varchar(128));
declare @curT cursor;
if isnull(@where, '')= ''
begin
	set @curT = cursor for select tablename, lvl from #tmp order by lvl desc
	open @curT;
	fetch next from @curT into @child, @lvl;
	while @@fetch_status = 0
	begin -- loop @curT
		if not exists (select 1 from @t where tablename=@child)
			insert into @t (tablename) values (@child);
		fetch next from @curT into @child, @lvl;
	end -- loop @curT
	close @curT;
	deallocate @curT;

	select  @sqlcmd = @sqlcmd + 'delete from ' + tablename + @crlf from @t ;
	print @sqlcmd;
end
else
begin 
	declare curT cursor for
	select  lvl, id
	from #tmp
	order by lvl desc;

	open curT;
	fetch next from curT into  @lvl, @id;
	while @@FETCH_STATUS =0
	begin
		set @i=0;
		if @lvl =0
		begin -- this is the root level
			select @sqlcmd = 'delete from ' + tablename from #tmp where id = @id;
		end -- this is the roolt level

		while @i < @lvl
		begin -- while

			select top 1 @child=TableName, @parent=ParentTable from #tmp where id <= @id-@i and lvl <= @lvl-@i order by lvl desc, id desc;
			set @curFK = cursor for
			select object_id from sys.foreign_keys 
			where parent_object_id = object_id(@child)
			and referenced_object_id = object_id(@parent)

			open @curFK;
			fetch next from @curFk into @fk_object_id
			while @@fetch_status =0
			begin -- @curFK

				if @i=0
					set @sqlcmd = 'delete from ' + @child + @crlf +
					'from ' + @child + @crlf + 'inner join ' + @parent  ;
				else
					set @sqlcmd = @sqlcmd + @crlf + 'inner join ' + @parent ;

				;with c as 
				(
					select child = object_schema_name(fc.parent_object_id)+'.' + object_name(fc.parent_object_id), child_col=c.name
					, parent = object_schema_name(fc.referenced_object_id)+'.' + object_name(fc.referenced_object_id), parent_col=c2.name
					, rnk = row_number() over (order by (select null))
					from sys.foreign_key_columns fc
					inner join sys.columns c
					on fc.parent_column_id = c.column_id
					and fc.parent_object_id = c.object_id
					inner join sys.columns c2
					on fc.referenced_column_id = c2.column_id
					and fc.referenced_object_id = c2.object_id
					where fc.constraint_object_id=@fk_object_id
				)
					select @sqlcmd =@sqlcmd +  case rnk when 1 then ' on '  else ' and ' end 
					+ @child +'.'+ child_col +'='  +  @parent   +'.' + parent_col
					from c;
					fetch next from @curFK into @fk_object_id;
			end --@curFK
			close @curFK;
			deallocate @curFK;
			set @i = @i +1;
		end --while
		print @sqlcmd + @crlf + @where + ';';
		print '';
		fetch next from curT into  @lvl, @id;
	end
	close curT;
	deallocate curT;
end

go

We will get the following result, which we can then copy to another query window and execute.

delete from dbo.Q1
from dbo.Q1
inner join dbo.P1 on dbo.Q1.Q1_c1=dbo.P1.P1_c1
inner join dbo.N1 on dbo.P1.P1_c1=dbo.N1.N1_c1 and dbo.P1.P1_c3=dbo.N1.N1_c3
inner join dbo.M on dbo.N1.N1_c1=dbo.M.M_c1
where M.id=2;
 
delete from dbo.Q2
from dbo.Q2
inner join dbo.P2 on dbo.Q2.Q2_c1=dbo.P2.id
inner join dbo.N1 on dbo.P2.P2_c2=dbo.N1.N1_c2
inner join dbo.M on dbo.N1.N1_c1=dbo.M.M_c1
where M.id=2;
 
delete from dbo.Q3
from dbo.Q3
inner join dbo.P2 on dbo.Q3.Q3_c2=dbo.P2.P2_c2 and dbo.Q3.Q3_c3=dbo.P2.P2_c3
inner join dbo.N1 on dbo.P2.P2_c2=dbo.N1.N1_c2
inner join dbo.M on dbo.N1.N1_c1=dbo.M.M_c1
where M.id=2;
 
delete from dbo.Q2
from dbo.Q2
inner join dbo.P2 on dbo.Q2.Q2_c1=dbo.P2.id
inner join dbo.N2 on dbo.P2.P2_c3=dbo.N2.N2_c3 and dbo.P2.P2_c4=dbo.N2.N2_c4
inner join dbo.M on dbo.N2.N2_c2=dbo.M.M_c2 and dbo.N2.N2_c3=dbo.M.M_c3
where M.id=2;
 
delete from dbo.Q3
from dbo.Q3
inner join dbo.P2 on dbo.Q3.Q3_c2=dbo.P2.P2_c2 and dbo.Q3.Q3_c3=dbo.P2.P2_c3
inner join dbo.N2 on dbo.P2.P2_c3=dbo.N2.N2_c3 and dbo.P2.P2_c4=dbo.N2.N2_c4
inner join dbo.M on dbo.N2.N2_c2=dbo.M.M_c2 and dbo.N2.N2_c3=dbo.M.M_c3
where M.id=2;
 
delete from dbo.P3
from dbo.P3
inner join dbo.N2 on dbo.P3.P3_c1=dbo.N2.id
inner join dbo.M on dbo.N2.N2_c2=dbo.M.M_c2 and dbo.N2.N2_c3=dbo.M.M_c3
where M.id=2;
 
delete from dbo.P1
from dbo.P1
inner join dbo.N1 on dbo.P1.P1_c1=dbo.N1.N1_c1 and dbo.P1.P1_c3=dbo.N1.N1_c3
inner join dbo.M on dbo.N1.N1_c1=dbo.M.M_c1
where M.id=2;
 
delete from dbo.Q3
from dbo.Q3
inner join dbo.N2 on dbo.Q3.Q3_c1=dbo.N2.id
inner join dbo.M on dbo.N2.N2_c2=dbo.M.M_c2 and dbo.N2.N2_c3=dbo.M.M_c3
where M.id=2;
 
delete from dbo.P2
from dbo.P2
inner join dbo.N2 on dbo.P2.P2_c3=dbo.N2.N2_c3 and dbo.P2.P2_c4=dbo.N2.N2_c4
inner join dbo.M on dbo.N2.N2_c2=dbo.M.M_c2 and dbo.N2.N2_c3=dbo.M.M_c3
where M.id=2;
 
delete from dbo.P2
from dbo.P2
inner join dbo.N1 on dbo.P2.P2_c2=dbo.N1.N1_c2
inner join dbo.M on dbo.N1.N1_c1=dbo.M.M_c1
where M.id=2;
 
delete from dbo.N1
from dbo.N1
inner join dbo.M on dbo.N1.N1_c1=dbo.M.M_c1
where M.id=2;
 
delete from dbo.N2
from dbo.N2
inner join dbo.M on dbo.N2.N2_c2=dbo.M.M_c2 and dbo.N2.N2_c3=dbo.M.M_c3
where M.id=2;
 
delete from dbo.M
where M.id=2;

Deleting All Data From All Tables

If you set @where to NULL or '' (empty string), the script will generate a bunch of delete table statements without any table joins and create them in the order they need to be executed as shown below.

delete from dbo.Q1
delete from dbo.Q2
delete from dbo.Q3
delete from dbo.P3
delete from dbo.P1
delete from dbo.P2
delete from dbo.N1
delete from dbo.N2
delete from dbo.M
Summary

When the FK DELETE cascade option is not "enabled", deleting data from tables with FKs in the right dependency order is no easy task. In this tip, a recursive stored procedure is created to traverse through all the FKs which have a common ancestor table, and then all the child / parent FK relationship will be recorded together with the information of how deep (i.e. level) this FK is from the ancestor, so we can use the info to delete table(s) starting from the leaf level up to the ancestor table at the root level.

Table cleanup can be a lengthy topic with many scenarios, such as deleting all tables in a database, deleting one or a few tables, or just deleting a few records in one master table which are referenced by multiple level of other tables. The solution can be used to delete or truncate tables by disabling/re-enabling, or dropping/recreating FKs, or temporarily modifying FKs to enable the delete cascade option. In this tip, I just tried to solve the most complex scenario to me, which I could not find a solution.

Next Steps

This generator script can be easily modified to run the dynamically generated SQL directly instead of printing out the statements, but sometimes it is better to first see the code that is going to be executed.

There are many good foreign key related table manipulation tips, please check out the following:



sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Jeffrey Yao Jeffrey Yao is a senior SQL Server consultant, striving to automate DBA work as much as possible to have more time for family, life and more automation.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Monday, October 25, 2021 - 3:34:29 PM - Dave Back To Top (89357)
@Jeffery to fix the nesting level exceeded (limit 32). I just added an additional exit condition in usp_SearchFK

while (@@fetch_status = 0 and @lvl <= 20) -- Or what ever level you want to bail on

this happens when you have a table dependent on itself (Parent, Child in the same table)

Saturday, February 6, 2021 - 5:05:52 AM - Maheera Jazi Back To Top (88190)
has anyone found a way to get around the error?

Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32). @Jeffery

Wednesday, February 3, 2021 - 7:31:38 PM - Maheera Back To Top (88148)
@WJ how you solve this issue!
Does anyone have a version of this script that would use aliases for table names during script creation? This script has errors if one of the tables has multiple foreign keys from the same table. Any help is appreciated

Wednesday, September 2, 2020 - 9:56:05 PM - WJ Back To Top (86406)
Does anyone have a version of this script that would use aliases for table names during script creation? This script has errors if one of the tables has multiple foreign keys from the same table. Any help is appreciated

Sunday, October 20, 2019 - 2:40:45 PM - Scott St.Cyr Back To Top (82835)

Great article.  Was very helpful.  I took and developed two SPROCs based on the above.  I also updated them to our coding style, so I apologize if the field and name changes confuse anyone.

The SPROC [Util.Table.DependencyList] returns the dependency list, same as above.

The SPROC [Util.Table.CascadeDelete] implements the cascade deletion, and gives options to wrap it in a transaction, just return the SQL, or execute the SQL.  I also ran into the problem Jonathan Roberts said where the same two tables have more than one FK between them.  I hacked it with a TOP(1) so it would run (albeit imperfectly), but would love a better solution.

Hope this helps. 

===================== Util.Table.DependencyList ============================

-- ========================================================

/*

Name: [Util.Table.DependencyList]  @TableName VARCHAR(255) 

Description: For the specified Table, retrieves the dependency list.

 

Sample Use:   

 

    EXEC [Util.Table.DependencyList] @TableName = 'dbo.[Part.Material.Data]'

*/

-- ========================================================

CREATE OR ALTER PROCEDURE dbo.[Util.Table.DependencyList]

        @TableName VARCHAR(255) -- use two part name convention

        , @RecurseLevel INT = 0 -- do not change

        , @ParentTable VARCHAR(255) = '' -- do not change

        , @Debug BIT = 0

AS

BEGIN

    SET nocount on;

    DECLARE @dbg BIT = @Debug

          , @curS cursor

    ;

 

    -- The temp table is shared between this SPROC and the [Util.Table.CascadeDelete] SPROC

    IF object_id('tempdb..#TempTable', 'U') IS NULL

        CREATE TABLE #TempTable (

                id int, 

                tablename VARCHAR(255), 

                RecurseLevel int, 

                ParentTable VARCHAR(255)

                );

 

 

    IF @RecurseLevel = 0

 

        insert into #TempTable (tablename, RecurseLevel, ParentTable)

            SELECT @TableName, @RecurseLevel, Null;

    

    ELSE

 

        insert into #TempTable (tablename, RecurseLevel, ParentTable)

            SELECT @TableName, @RecurseLevel, @ParentTable;

    

    IF @dbg=1

        print replicate('--

        --', @RecurseLevel) + 'RecurseLevel ' + cast(@RecurseLevel as varchar(10)) + ' = ' + @TableName;

    

    IF not exists (SELECT * FROM sys.foreign_keys WHERE referenced_object_id = object_id(@TableName))

        return;

 

    ELSE

    BEGIN -- ELSE

        SET @ParentTable = @TableName

        SET @curS = 

                cursor for

                    SELECT tablename=CONCAT(object_schema_name(parent_object_id),'.[',object_name(parent_object_id),']')

                    FROM sys.foreign_keys 

                    WHERE referenced_object_id = object_id(@TableName)

                    AND parent_object_id <> referenced_object_id; -- add this to prevent self-referencing which can create a indefinitive loop;

 

        OPEN @curS;

        FETCH NEXT FROM @curS INTO @TableName;

 

        WHILE @@fetch_status = 0

        BEGIN --while

            SET @RecurseLevel += 1;

            -- recursive call

            exec dbo.[Util.Table.DependencyList] @TableName, @RecurseLevel, @ParentTable, @dbg;

            SET @RecurseLevel -= 1;

            

            FETCH NEXT FROM @curS INTO @TableName;

        END --while

        CLOSE @curS;

        deallocate @curS;

    END -- ELSE

 

    IF @RecurseLevel = 0

        SELECT * FROM #TempTable;

 

    return;

END

 

 

===================== Util.Table.CascadeDelete ============================

-- ========================================================

/*

Name: [Util.Table.CascadeDelete] @TableName VARCHAR(255), @BID SMALLINT, @ID INT 

Description: For the specified Table, forces a CASCADE delete on the item 

    AND ALL FOREIGN KEY dependencies.  Be very careful with this, as it deletes 

    far more than just the row being deleted.

 

Sample Use:   

 

    BEGIN TRANSACTION 

        DECLARE @BID SMALLINT = 1;

        DECLARE @ID INT = 1000;

        SELECT * FROM [Contact.Data] WHERE BID = @BID AND ID = @ID

        SELECT * FROM [Order.Data] WHERE BID = @BID AND CompanyID = @ID

 

        EXEC [Util.Table.CascadeDelete] @TableName = 'dbo.[Company.Data]', @BID = @BID, @ID = @ID, @ReturnSQL = 1, @ExecuteSQL = 0, @ReturnAffectedTables = 1

 

        SELECT * FROM [Contact.Data] WHERE BID = @BID AND ID = @ID

        SELECT * FROM [Order.Data] WHERE BID = @BID AND CompanyID = @ID

    ROLLBACK TRANSACTION 

*/

-- ========================================================

CREATE OR ALTER PROCEDURE dbo.[Util.Table.CascadeDelete]

        @TableName VARCHAR(255) -- use two part name convention

        , @BID SMALLINT 

        , @ID INT

        , @RollbackOnError BIT = 1

        , @ExecuteSQL BIT = 0

        , @ReturnSQL BIT = 1

        , @ReturnAffectedTables BIT = 1

AS

BEGIN

    SET NOCOUNT ON;

 

    DROP TABLE IF EXISTS #DependencyList;

 

    CREATE TABLE #DependencyList (

            id int, 

            tablename VARCHAR(255), 

            RecurseLevel int, 

            ParentTable VARCHAR(255)

            );

 

    INSERT INTO #DependencyList 

        EXEC dbo.[Util.Table.DependencyList] @TableName=@TableName, @debug=0;

 

    DECLARE @where VARCHAR(MAX) = CONCAT('  WHERE ', @TableName, '.BID = @BID AND ', @TableName, '.ID = @ID ')

          , @FKCursor cursor

          , @FK_ObjectID int

          , @crlf char(2)=char(0x0d)+char(0x0a)

          , @ChildTable VARCHAR(255)

          , @ParentTable VARCHAR(255)

          , @RecurseLevel int, @CurrentID int

          , @i int

    ;

 

    DECLARE @SQLCmd NVARCHAR(MAX)=CONCAT(

                  'DECLARE @BID SMALLINT =' , @BID, ';', @crlf

                , 'DECLARE @ID INT =' , @ID, ';', @crlf, @crlf

          );

 

 

    DECLARE curT CURSOR FOR

        SELECT  RecurseLevel, id

        FROM #DependencyList

        ORDER BY RecurseLevel DESC;

 

    OPEN curT;

    FETCH NEXT FROM curT into  @RecurseLevel, @CurrentID;

 

    WHILE @@FETCH_STATUS = 0

    BEGIN

        SET @i=0;

        IF @RecurseLevel = 0

            SELECT @SQLCmd += CONCAT('  DELETE FROM ', tablename)

            FROM #DependencyList 

            WHERE id = @CurrentID;

 

        WHILE @i < @RecurseLevel

        BEGIN -- WHILE

 

            SELECT TOP(1) @ChildTable=TableName, @ParentTable=ParentTable 

            FROM #DependencyList 

            WHERE id <= @CurrentID-@i AND RecurseLevel <= @RecurseLevel-@i 

            ORDER BY RecurseLevel DESC, id DESC;

 

            SET @FKCursor = CURSOR FOR

                -- There is a bug if there are multiple FKs to the same table.  For now, the only (partial) workaround is to only use one.

                SELECT TOP(1) object_id 

                FROM sys.foreign_keys 

                WHERE parent_object_id = object_id(@ChildTable)

                AND referenced_object_id = object_id(@ParentTable)

 

            OPEN @FKCursor;

            FETCH NEXT FROM @FKCursor INTO @FK_ObjectID

            WHILE @@fetch_status =0

            BEGIN -- @FKCursor

 

                IF @i=0

                    SET @SQLCmd += CONCAT('  DELETE ', @ChildTable, @crlf, '  FROM ', @ChildTable, @crlf, '  JOIN ', @ParentTable);

                ELSE

                    SET @SQLCmd += CONCAT(@crlf, '  JOIN ', @ParentTable);

 

                WITH c AS 

                (

                    SELECT child = object_schema_name(fc.parent_object_id)+'.' + object_name(fc.parent_object_id), child_col=c.name

                    , parent = object_schema_name(fc.referenced_object_id)+'.' + object_name(fc.referenced_object_id), parent_col=c2.name

                    , rnk = row_number() over (ORDER BY (SELECT null))

                    FROM sys.foreign_key_columns fc

                    JOIN sys.columns c ON fc.parent_column_id = c.column_id AND fc.parent_object_id = c.object_id

                    JOIN sys.columns c2 ON fc.referenced_column_id = c2.column_id AND fc.referenced_object_id = c2.object_id

                    WHERE fc.constraint_object_id = @FK_ObjectID

                )

                    SELECT @SQLCmd += (CASE rnk when 1 then ' ON '  ELSE ' AND ' END) 

                                        + (@ChildTable +'.'+ child_col +'='  +  @ParentTable +'.' + parent_col)

                    FROM c;

 

                FETCH NEXT FROM @FKCursor into @FK_ObjectID;

            END --@FKCursor

 

            CLOSE @FKCursor;

            DEALLOCATE @FKCursor;

            SET @i += 1;

        END --WHILE

 

        SET @SQLCmd += CONCAT(@crlf, @where, ';', @crlf, @crlf);

 

        FETCH NEXT FROM curT into  @RecurseLevel, @CurrentID;

 

    END

    

    CLOSE curT;

    DEALLOCATE curT;

 

    IF (@RollbackOnError = 1)

    BEGIN

        SET @SQLCmd = CONCAT(

                'BEGIN TRANSACTION' , @crlf

                , 'BEGIN TRY' , @crlf

                , @SQLCmd , @crlf

                , 'COMMIT TRANSACTION' , @crlf

                , 'END TRY' , @crlf

                , 'BEGIN CATCH' , @crlf

                , '  ROLLBACK TRANSACTION;' , @crlf

                , '  THROW;' , @crlf

                , 'END CATCH' , @crlf

        );

 

    END;

 

    IF (@ExecuteSQL = 1)

        EXEC sp_executesql @SQLCmd ;

 

    IF (@ReturnSQL = 1)

        SELECT @SQLCmd as SQLCommand;

 

    IF (@ReturnAffectedTables = 1)

        SELECT DISTINCT tablename as TableName

        FROM #DependencyList;

 

    DROP TABLE #DependencyList;

END

Tuesday, July 23, 2019 - 5:23:28 PM - Victor Feinstein Back To Top (81844)

Hi Jeffrey,

wonderful use of recursion very helpful. Is it feasible to have another version of your code that traverses the schema hierarchy in reverse of a DELETE in order to perform inserts?

that would be extremely useful, and you would know exectly how to change it.

If you cannot change it, can you please make some suggestions?

thanks in advance! very impressive example!

Tuesday, June 11, 2019 - 5:55:30 AM - Upendra Back To Top (81406)

Everything was working fine but after a few days, I am getting below error.

Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

Has anyone got the solution for the error?

Monday, July 2, 2018 - 1:29:24 PM - Paul Back To Top (76502)

 This is a wonderful script.. 

has anyone found a way to get around the error ?

Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

Wednesday, January 24, 2018 - 12:25:48 AM - sarat Back To Top (75027)

This is the script which I have been looking for years... 

 

Tuesday, December 19, 2017 - 9:02:36 AM - cascador Back To Top (74241)

 Hi,

 

thanks for this really helpful script !!

it work perfectly !!

Many thanks !

Friday, August 25, 2017 - 1:45:43 AM - jeff_yao Back To Top (65284)

Thanks @Kyle for reading the tip. I actually wrote a PowerShell version to do this work. In the PS version, I have the flexibility to temporarily modify the FKs (like enable/disable/drop/recreate) and I find this is more productive, however, in heavy-auditing environment where all these DDLs are strictly controlled, I will use the T-SQL approach as outlined in the tip to do the work.

Wednesday, August 23, 2017 - 5:09:43 PM - Kyle Back To Top (65211)

 

I have been trying to do this for a long time.  I was able to change the script so it would use alias names in the cases where a table is listed multiple times.  I am currently testing.  Have you consisdered moving it to an application to deal with some of the limitiations of SQL such as the recursion ?

Monday, July 24, 2017 - 10:06:36 AM - Jonathan Roberts Back To Top (63166)

Here's a script to set up some test tables with data added to show the problem:

-- CREATE TABLES
create table Header(HeaderId int identity(1,1) not null primary key,HeaderText varchar(20))
create table Detail(DetailId int identity(1,1) not null primary key,
                    HeaderId int null constraint FK_Detail_Header REFERENCES Header(HeaderId),
                    PreviousHeaderId int null constraint FK_Detail_Header1 REFERENCES Header(HeaderId))
create table DetailDetail(DetailDetailId int identity(1,1) primary key not null, 
                          DetailId int null constraint FK_DetailDetail_Detail REFERENCES Detail(DetailId))

-- INSERT DATA
insert into Header(HeaderText) VALUES ('Hello')
insert into Header(HeaderText) VALUES ('Goodbye')
insert into Detail(HeaderId,PreviousHeaderId) VALUES (1,NULL)
insert into Detail(HeaderId,PreviousHeaderId) VALUES (2,1)
insert into DetailDetail(DetailId) VALUES (1)

 

-- Script generated to delete data

DELETE dbo.DetailDetail
  FROM dbo.DetailDetail
 INNER JOIN dbo.Detail ON dbo.DetailDetail.DetailId=dbo.Detail.DetailId
 INNER JOIN dbo.Header ON dbo.Detail.HeaderId=dbo.Header.HeaderId
 INNER JOIN dbo.Header ON dbo.Detail.PreviousHeaderId=dbo.Header.HeaderId
 WHERE Header.HeaderId=#xxxx#;

DELETE dbo.DetailDetail
  FROM dbo.DetailDetail
 INNER JOIN dbo.Detail ON dbo.DetailDetail.DetailId=dbo.Detail.DetailId
 INNER JOIN dbo.Header ON dbo.Detail.HeaderId=dbo.Header.HeaderId
 INNER JOIN dbo.Header ON dbo.Detail.PreviousHeaderId=dbo.Header.HeaderId
 WHERE Header.HeaderId=#xxxx#;

DELETE dbo.Detail
  FROM dbo.Detail
 INNER JOIN dbo.Header ON dbo.Detail.PreviousHeaderId=dbo.Header.HeaderId
 WHERE Header.HeaderId=#xxxx#;

DELETE dbo.Detail
  FROM dbo.Detail
 INNER JOIN dbo.Header ON dbo.Detail.PreviousHeaderId=dbo.Header.HeaderId
 WHERE Header.HeaderId=#xxxx#;

DELETE dbo.Header
 WHERE Header.HeaderId=#xxxx#;

I would manually change the bold statement to:

DELETE dbo.DetailDetail
  FROM dbo.DetailDetail
 INNER JOIN dbo.Detail ON dbo.DetailDetail.DetailId=dbo.Detail.DetailId
 INNER JOIN dbo.Header ON dbo.Header.HeaderId IN (dbo.Detail.HeaderId, dbo.Detail.PreviousHeaderId)
 WHERE Header.HeaderId=#xxxx#;


-- Drop tables:
drop table DetailDetail
drop table Detail
drop table Header

 

I appreciate the work you must have put into the code.

 

Monday, July 24, 2017 - 9:47:48 AM - Jonathan Roberts Back To Top (63165)

I've manually amended the generated script to chage the two joins to the same table to just one with an "IN" clause and to use table aliases, something like this:

From:

DELETE dbo.DetailDetail
  FROM dbo.DetailDetail
 INNER JOIN dbo.Detail ON dbo.DetailDetail.DetailId = dbo.Detail.DetailId 
 INNER JOIN dbo.Header ON dbo.Detail.HeaderId = dbo.Header.HeaderId
 INNER JOIN dbo.Header ON dbo.Detail.PreviousHeaderId = dbo.Header.HeaderId
 WHERE dbo.Header.Code = @Code

To:

DELETE dd
  FROM dbo.DetailDetail dd
 INNER JOIN dbo.Detail d ON d.DetailId = dd.DetailId
 INNER JOIN dbo.Header h ON h.HeaderId IN (d.HeaderId, d.PreviousHeaderId)
 WHERE h.Code = @Code

The script you provided is still very useful as the manual amendments are minimal compared to writing the scriptfrom scratch.

Monday, July 24, 2017 - 7:04:07 AM - Jonathan Roberts Back To Top (63161)

An excellent script. I think there is a small problem when a table with FKs has two different columns pointing to the same parent table and I'm not sure how to fix it. e.g. three  tables:

Header(HeaderId)

Detail(DetailId (PK), HeaderId (FK), PreviousHeaderId (FK)) -- both FK columns have FKs pointing to  Header(HeaderId)

DetailDetail(DetailDetailId (PK), DetailId (FK))

The SQL generated is a join on both columns: 

DELETE DetailDetail dd

INNER JOIN Detail d ON d.DetailId = dd.DetailId

INNER JOIN Header h ON h.HeaderId = d.HeaderId

INNER JOIN Header h2 ON h2.HeaderId = d.PreviousHeaderId

 

The PreviousHeaderId could be NULL so no rows might be deleted

It also doesn't alias the columns so the actual SQL looks more like this:

DELETE DetailDetail dd

INNER JOIN Detail d ON d.DetailId = dd.DetailId

INNER JOIN Header h ON h.HeaderId = d.HeaderId

INNER JOIN Header ON h.HeaderId = d.PreviousHeaderId

Monday, May 22, 2017 - 2:03:04 PM - jeff_yao Back To Top (55966)

Thanks for reading the tip, @Lev. 

Max recursion level of 32 (for a stored procedure) seems to be a "hard-coded" limit in SQL Server engine as shown here https://technet.microsoft.com/en-us/library/ms190607(v=sql.105).aspx

Since this limit does impact you and some other users, I will do some research and see whether I can solve this issue from another perspective, and will update you if I figure it out.

 

Kind regards,

Jeff_yao

Monday, May 22, 2017 - 7:51:09 AM - Lev Back To Top (55960)

 

Hi everybody! 

First, i woult to thank the author for a great article.

Secondly, does anybody has a answer fro @shanker question.

What can I do if my regretion deapth is over 32 levels?

 

Thanks 

Thursday, May 4, 2017 - 9:12:52 AM - [email protected] Back To Top (55439)

Thanks for this -  one of my hierarchies had 142 tables that needed to be cleared out. That would have taken a very long time to code by hand. Good job!

 

Monday, March 6, 2017 - 7:42:44 AM - shanker Back To Top (47374)

I am running your query posted and sp for getting delete query of all tables which are nested by foreign key.

 

But i am getting below error when run sp 'exec dbo.usp_SearchFK' :

 

Msg 217, Level 16, State 1, Procedure usp_SearchFK, Line 42

Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

 

How i will resolve this.

Please reply as soon as possible.

Monday, October 19, 2015 - 12:49:59 AM - Bill Back To Top (38928)

@Jeff
Totally agree - I have burned a lot of midnight oil grappling with data removal :).

My production systems are 24x7 and heavily OLTP. My Dev/QA/Test/UAT systems are perpetually in development. I work with ~100 .Net developers, and  I am the sole DBA (albeit our load tester knows SQL Server well enough :). I know my developers have sloppily enforced RI at the application level. I know that in part because the app submits a mix of ORM-generated calls and custom-generated ADO.Net calls (with nobody knows all the calls), in part because RI violations are occasionally raised, in part because I have seen FKs readily dropped when they get "in the way", in part because cascading deletes are randomly imposed or omitted, and in part because untrusted FKs existed when I started. Before I arrived, I suspect the developers (all follow the "Agile" method) refused to delete because "disk space is cheap" and they could "worry about deleting later". After accumulating years worth of now-unneeded data, and after I developed a solid restore & DR strategy (both of which ballooned our systems' disk space needs), it was discovered that  disk space is now actually notably "expensive" :). I thus was tasked with developing a data removal process (based upon an ever-changing number of tables that "needed" to be purged), using a sliding monthly window. The fastest method I tested was to partition switch using that sliding window. But the current db design heavily relies upon surrogate primary keys ([ID] bigint with the identity property), which means partition switching requires wholesale changes to DDL (along with load testing and regression testing of legacy db designs that are still in production).

On occasion some developers have refused to declare RI, for the sake of "insert performance". As a result I also had to create my own schema information, modeled after sys.foreign_key_columns,  sys.tables, sys.columns, and various WHERE clause's SARGs (both parents and children can have SARGs). To make matters more complicated, the databases I support can harness various schema (depending upon which build was released for a given set of customers). So my approach has had to be flexible, tailored for various current and future database designs (my code is very similar to your code, albeit using custom information schema:). When needed (I monitor WRITELOG waits and delete progress), I can permanently or temporarily scale up TOP (N) on a per-table basis, or, I can permanently or temporarily throttle a deletion rate. That is why GetTop returns the lowest row count that I am willing to delete (in a loop, with SET DEADLOCK_PRIORITY LOW and limited resubmittals).

I could truncate tables during maintenance windows, but the largest table (prophetically called [Transactions] :)) has between 12/13ths and ~23/24ths (i.e. the vast majority) of its rows needing to be preserved. Moving those rows into a permanent "temp" table would consume about an hour of time on SSDs (sp_rename avoids the need to move data twice) for my largest systems, and that is time I don't have :).  [Transactions] has a few non-clustered indexes that must INCLUDE every column (thanks to the ORM). Those indexes roughly triple the space used by [Transactions], which is part of the reason why it takes a considerable amount of time to rebuild the vast majority of the [Transactions]. Because the vast majority of  [Transactions] have to be preserved, a sliding window partition switch is the fastest method I tested. But I have to contend with legacy app code, legacy db designs, and (what I perceive as) the unpredictable behavior of the ORM.


I suspect our databases will be heavily redesigned shortly, due to scalability concerns rooted in the ORM, the app, and their impact upon SQL Server's current CPU demands. My heaviest systems (typically an 8-way) are currently handling up to ~2000 batches/sec. That means my systems can't tolerate long-running transactions, where "long" is measured in tens of milliseconds. Once management is committed to a db redesign, I will be pushing hard for the SARGs' datetime columns to be added to PKs/unique indexes (and thus the FKs) for any table that needs to be deleted. Right now that is a tough pill to swallow. Assuming that redesign passes load testing, I (or preferably, the developers) will be able to harness sliding window partition switches (on an OLTP system), during regularly scheduled, but brief, maintenance windows :).

So the delete approach (I use) is an imperfect compromise, at best (it is the slowest approach). It effectively almost doubles my system's logging needs, and it can drive up CPU time (depends upon the number of nested loops in a query plan). Even so, the sum of PAGEIOLATCH% and WRITELOG wait rates is about half of my OLTP systems' SOS_SCHEDULER_YIELD wait rates (and LOCK% wait rates are 1/10th lower than the IO waits). While I must retain data using a monthly sliding window, my deletes use daily precision (a small consolation). Anyone who contemplates such a DELETE approach should know their system's performance bottlenecks (wait rates), know roughly how many locks and how much CPU time each DELETE statement will consume,  know their system's lock memory,, and know whether the time needed for a truncate or a partition switch (within or without a maintenance window) is intolerable, before they take dive at the slowest choice of all (a logged DELETE). IMO, using DELETEs in a loop should be the option of last resort. Caveat Emptor

Sunday, October 18, 2015 - 1:26:19 PM - jeff_yao Back To Top (38924)

@Bill, I can understand your concern that when deleting big tables, "delete" is really not an ideal method in terms of performance and cost.

I generally rely on "truncate" method by first saving FKs then removing FKs, then truncating tables and then resetting up the FKs.

Saturday, October 17, 2015 - 4:37:39 PM - Bill Back To Top (38921)

I typically need to dynamically generate a TOP (N) statement (and get the total number of rows in the tables being deleted):


CREATE PROCEDURE [GetTop]
 (
  @DatabaseName sysname,
  @SchemaName sysname,
  @TableName sysname,
  @TopRowCount int OUTPUT,
  @TableRows int OUTPUT
 )
AS
SET NOCOUNT ON
CREATE TABLE [#Statistics] ([Density] smallint, [Rows] int)

DECLARE
 @SQL nvarchar(max), 
 @Density smallint

SET @SQL =
 N'USE ' + QUOTENAME(@DatabaseName) + '
 INSERT [#Statistics]
  SELECT
  CAST(ROUND(SUM
     (
     CASE WHEN (p.index_id IN(0,1) AND au.type = 1)
     THEN p.rows+1
     ELSE 0
     END) * 1.0
     /
     (CASE
      WHEN (SUM(au.total_pages)=0)
     THEN 1
     ELSE SUM(au.total_pages)
     END
     ),0) AS int),
  CAST(SUM
    (
    CASE WHEN (p.index_id IN(0,1) AND au.type = 1)
    THEN p.rows+1
    ELSE 1
    END
    ) AS int)
  FROM [sys].[tables] AS t
  JOIN [sys].partitions AS p
   ON t.OBJECT_ID = p.OBJECT_ID
  JOIN [sys].allocation_units AS au
   ON p.hobt_id = au.container_id
  WHERE t.name = ' + QUOTENAME(@TableName,'''') + '
   AND SCHEMA_NAME(t.schema_id) = ' + QUOTENAME(@SchemaName,'''')

EXEC sp_executesql @SQL

SELECT @Density = [Density], @TableRows = [Rows]
 FROM [#Statistics]

SELECT @TopRowCount = ISNULL(@Density, 1)

IF @TopRowCount > ISNULL(@TableRows, 1)
BEGIN
 SET @TopRowCount = ISNULL(@TableRows, 1)
END

IF @TopRowCount > 5000 
BEGIN
 SET @TopRowCount = 5000  -- 5000 rows can still easily escalate to a table lock when there are FKs.
END

IF @TopRowCount = 0
BEGIN
 SET @TopRowCount = 1  -- must try to delete at least one row
END
GO

Saturday, October 17, 2015 - 5:51:05 AM - BobT Back To Top (38919)

Great article, much appreciated.

Thursday, October 15, 2015 - 2:35:42 PM - jeff_yao Back To Top (38905)

I need to add that the current script in the tip cannot handle tables with self-referencing FKs, i.e. a column in a table referring to another column in the SAME table. The problem with this self-referencing is the recursive logic (in current tip) will do the recursion indefinitely.

This self-referencing concept can be extended to multiple tables, like table A referring to table B and table B referring to table C and table C referring back to table A. In all these scenarios, we cannot tell who is a parent table (i.e. referenced table) and who is a child table (i.e. referring table), and thus recursion logic is hard to do the work.














get free sql tips
agree to terms