Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Show Cascade Delete Tree


Show Cascade Delete Tree

Author
Message
Bill Talada
Bill Talada
Old Hand
Old Hand (385 reputation)Old Hand (385 reputation)Old Hand (385 reputation)Old Hand (385 reputation)Old Hand (385 reputation)Old Hand (385 reputation)Old Hand (385 reputation)Old Hand (385 reputation)

Group: General Forum Members
Points: 385 Visits: 1818
Comments posted to this topic are about the item Show Cascade Delete Tree
sjsubscribe
sjsubscribe
SSC Veteran
SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)

Group: General Forum Members
Points: 285 Visits: 595
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




Iwas Bornready
Iwas Bornready
SSCrazy Eights
SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)

Group: General Forum Members
Points: 8712 Visits: 885
Thanks for the script.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search