Show Cascade Delete Tree

  • Bill Talada

    SSChampion

    Points: 11956

    Comments posted to this topic are about the item Show Cascade Delete Tree

  • sjsubscribe

    SSCrazy

    Points: 2080

    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

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    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