December 18, 2009 at 4:26 pm
Comments posted to this topic are about the item Show Cascade Delete Tree
March 18, 2010 at 10:11 am
Thanks. Since I needed one for update cascade tree, I modified yours. Now I can use either one depending on the situation. --SJ
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[ShowUpdateTree]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[ShowUpdateTree]
GO
CREATE PROCEDURE dbo.ShowUpdateTree
@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),
@updaction 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, updaction varchar(20))
-- dependency list
insert into @dep
select distinct object_name(parent_object_id), object_name(referenced_object_id), 0, update_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', updaction as 'UpdateAction' 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,'ExecIsUpdateTrigger')=1)
print 'Has "Instead Of Update" Trigger so parents cannot cascade delete it.
'
--1
select @c1=min(child) from @dep where parent = @p
while @c1 is not null
begin
select @updaction = updaction from @dep where child=@c1 and parent=@p
if @printdepth >=1 print @char9+@c1+' '+@updaction
--2
select @c2=min(child) from @dep where parent = @c1
while @c2 is not null
begin
select @updaction = updaction from @dep where child=@c2 and parent=@c1
if @printdepth >=2 print @char9+@char9+@c2+' '+@updaction
--3
select @c3=min(child) from @dep where parent = @c2
while @c3 is not null
begin
select @updaction = updaction from @dep where child=@c3 and parent=@c2
if @printdepth >=3 print @char9+@char9+@char9+@c3+' '+@updaction
--4
select @c4=min(child) from @dep where parent = @c3
while @c4 is not null
begin
select @updaction = updaction from @dep where child=@c4 and parent=@c3
if @printdepth >=4 print @char9+@char9+@char9+@char9+@c4+' '+@updaction
--5
select @c5=min(child) from @dep where parent = @c4
while @c5 is not null
begin
select @updaction = updaction from @dep where child=@c5 and parent=@c4
if @printdepth >=5 print @char9+@char9+@char9+@char9+@char9+@c5+' '+@updaction
--6
select @c6=min(child) from @dep where parent = @c5
while @c6 is not null
begin
select @updaction = updaction from @dep where child=@c6 and parent=@c5
if @printdepth >=6 print @char9+@char9+@char9+@char9+@char9+@char9+@c6+' '+@updaction
--7
select @c7=min(child) from @dep where parent = @c6
while @c7 is not null
begin
select @updaction = updaction from @dep where child=@c7 and parent=@c6
if @printdepth >=7 print @char9+@char9+@char9+@char9+@char9+@char9+@char9+@c7+' '+@updaction
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
May 19, 2016 at 1:23 pm
Thanks for the script.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy