Show Cascade Delete Tree

,

Often developers come to me wanting cascade deletes implemented through foreign key definitions. With this stored procedure I can quickly show them the child tables involved and which ones need changes in their foreign key definitions from 'no action' to 'cascade'.

Create the ShowDeleteTree procedure in any database and call it with a table name for example:

exec ShowDeleteTree 'PurchaseOrderHeaders'

Use text output so you can see the tree of foreign key tables involved. You may optionally pass a number from 1 to 9 of the depth you want to see. This limits output to something manageable since larger databases often result in pages of output.

Most databases contain a mixture of child deletes through triggers and through foreign keys. This procedure will help with the declarative referential integrity half. You'll still need to look through trigger code for the other half.

 

The output below is from running the following command in msdb:

exec ShowDeleteTree DTA_reports_database

 

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[ShowDeleteTree]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[ShowDeleteTree]
GO

CREATE PROCEDURE dbo.ShowDeleteTree
	@TableName varchar(128),
	@PrintDepth int = 9
AS  

-- print tree of dependencies for any table
declare 
	@p varchar(128),
	@c1 varchar(128),
	@c2 varchar(128),
	@c3 varchar(128),
	@c4 varchar(128),
	@c5 varchar(128),
	@c6 varchar(128),
	@c7 varchar(128),
	@delaction varchar(20),
	@char9 varchar(10)


set @p = @TableName
set @char9 = '-->'

-- view as text

-- hierarchy -------------------------------------------------------------
set nocount on
declare @loop int
declare @dep table( child varchar(100), parent varchar(100), pass int, delaction varchar(20))

-- dependency list
insert into @dep
select distinct object_name(parent_object_id), object_name(referenced_object_id), 0, delete_referential_action_desc
from sys.foreign_keys 
where parent_object_id <> referenced_object_id
order by 1,2



-- bottom: children that are never parents
update
	@dep
set
	pass = 1
where
	child not in 
		(
			select 
				parent
			from
				@dep
		)


-- middle to top: parents where children have been processed
set @loop = 2
while @loop < 10
begin
	update
		@dep
	set
		pass = @loop
	where
		pass = 0
	and
		parent in 
			(
				-- select parents where all children have been processed
				select
					parent
				from
					@dep d1
				where
					pass = 0
				and
					not exists
						(select * from @dep d2 where d1.child=d2.parent and d2.pass=0)
			)

	set @loop = @loop + 1
end

select parent as 'ImmediateParent', delaction as 'DeleteAction' from @dep where child = @p
print ''

print '-- children of'
print @p

if exists (select * 
from sys.tables t join sys.triggers tr on t.object_id=tr.parent_id 
where t.name = @p
and tr.is_instead_of_trigger=1
and objectproperty(tr.object_id,'ExecIsDeleteTrigger')=1)
	print 'Has "Instead Of Delete" Trigger so parents cannot cascade delete it.

'


--1
select @c1=min(child) from @dep where parent = @p
while @c1 is not null
begin
	select @delaction = delaction from @dep where child=@c1 and parent=@p
	if @printdepth >=1 print @char9+@c1+' '+@delaction
	--2
	select @c2=min(child) from @dep where parent = @c1
	while @c2 is not null
	begin
		select @delaction = delaction from @dep where child=@c2 and parent=@c1
		if @printdepth >=2 print @char9+@char9+@c2+' '+@delaction
		--3
		select @c3=min(child) from @dep where parent = @c2
		while @c3 is not null
		begin
			select @delaction = delaction from @dep where child=@c3 and parent=@c2
			if @printdepth >=3 print @char9+@char9+@char9+@c3+' '+@delaction
			--4
			select @c4=min(child) from @dep where parent = @c3
			while @c4 is not null
			begin
				select @delaction = delaction from @dep where child=@c4 and parent=@c3
				if @printdepth >=4 print @char9+@char9+@char9+@char9+@c4+' '+@delaction
				--5
				select @c5=min(child) from @dep where parent = @c4
				while @c5 is not null
				begin
					select @delaction = delaction from @dep where child=@c5 and parent=@c4
					if @printdepth >=5 print @char9+@char9+@char9+@char9+@char9+@c5+' '+@delaction
					--6
					select @c6=min(child) from @dep where parent = @c5
					while @c6 is not null
					begin
						select @delaction = delaction from @dep where child=@c6 and parent=@c5
						if @printdepth >=6 print @char9+@char9+@char9+@char9+@char9+@char9+@c6+' '+@delaction
						--7
						select @c7=min(child) from @dep where parent = @c6
						while @c7 is not null
						begin
							select @delaction = delaction from @dep where child=@c7 and parent=@c6
							if @printdepth >=7 print @char9+@char9+@char9+@char9+@char9+@char9+@char9+@c7+' '+@delaction
							select @c7=min(child) from @dep where parent = @c6 and child > @c7
						end
						--7
						select @c6=min(child) from @dep where parent = @c5 and child > @c6
					end
					--6
					select @c5=min(child) from @dep where parent = @c4 and child > @c5
				end
				--5
				select @c4=min(child) from @dep where parent = @c3 and child > @c4
			end
			--4
			select @c3=min(child) from @dep where parent = @c2 and child > @c3
		end
		--3
		select @c2=min(child) from @dep where parent = @c1 and child > @c2
	end
	--2
	select @c1=min(child) from @dep where parent = @p and child > @c1
end
--1

return 0
GO

Rate

4 (2)

Share

Share

Rate

4 (2)