Technical Article

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)