RE: Show Cascade Delete Tree

sjsubscribe — Thu, 18 Mar 2010 10:11:48 GMT

Thanks. Since I needed one for update cascade tree, I modified yours. Now I can use either one depending on the situation. --SJ[code="sql"]IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[ShowUpdateTree]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)DROP PROCEDURE [dbo].[ShowUpdateTree]GOCREATE PROCEDURE dbo.ShowUpdateTree @TableName varchar(128), @PrintDepth int = 9AS -- print tree of dependencies for any tabledeclare @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 = @TableNameset @char9 = '-->'-- view as text-- hierarchy -------------------------------------------------------------set nocount ondeclare @loop intdeclare @dep table( child varchar(100), parent varchar(100), pass int, updaction varchar(20))-- dependency listinsert into @depselect distinct object_name(parent_object_id), object_name(referenced_object_id), 0, update_referential_action_descfrom sys.foreign_keys where parent_object_id <> referenced_object_idorder by 1,2-- bottom: children that are never parentsupdate @depset pass = 1where child not in ( select parent from @dep )-- middle to top: parents where children have been processedset @loop = 2while @loop < 10begin 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 + 1endselect parent as 'ImmediateParent', updaction as 'UpdateAction' from @dep where child = @pprint ''print '-- children of'print @pif exists (select * from sys.tables t join sys.triggers tr on t.object_id=tr.parent_id where t.name = @pand tr.is_instead_of_trigger=1and objectproperty(tr.object_id,'ExecIsUpdateTrigger')=1) print 'Has "Instead Of Update" Trigger so parents cannot cascade delete it.'--1select @c1=min(child) from @dep where parent = @pwhile @c1 is not nullbegin 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 > @c1end--1return 0[/code]

Show Cascade Delete Tree

Bill Talada — Fri, 18 Dec 2009 16:26:35 GMT

Comments posted to this topic are about the item [B]Show Cascade Delete Tree[/B]

SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by William Talada / Show Cascade Delete Tree / Latest Posts

RE: Show Cascade Delete Tree

Show Cascade Delete Tree