Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Show Cascade Delete Tree Expand / Collapse
Author
Message
Posted Friday, December 18, 2009 4:26 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 12:08 PM
Points: 132, Visits: 848
Comments posted to this topic are about the item Show Cascade Delete Tree
Post #836708
Posted Thursday, March 18, 2010 10:11 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, December 20, 2012 1:03 PM
Points: 265, Visits: 589
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



Post #885688
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse