A SQL script to iterate through all your tables in MSSQL and show cascading deletes and dependencies

ShowCascadingDeletions.sql

-- Script to show delete dependencies for all tables
SET NOCOUNT ON;

-- Declare variables
DECLARE 
    @TableName varchar(128),
    @p varchar(128),
    @c1 varchar(128),
    @c2 varchar(128),
    @c3 varchar(128),
    @c4 varchar(128),
    @c5 varchar(128),
    @c6 varchar(128),
    @c7 varchar(128),
    @delaction varchar(20),
    @char9 varchar(10),
    @PrintDepth int = 9,
    @loop int

-- Create a cursor to iterate through all tables
DECLARE TableCursor CURSOR FOR 
SELECT name FROM sys.tables 
ORDER BY name;

OPEN TableCursor;
FETCH NEXT FROM TableCursor INTO @TableName;

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT '=========================================';
    PRINT 'ANALYZING TABLE: ' + @TableName;
    PRINT '=========================================';
    
    set @p = @TableName
    set @char9 = '-->'

    -- Create dependency table for current table
    declare @dep table(
        child varchar(100), 
        parent varchar(100), 
        pass int, 
        delaction varchar(20)
    )

    -- dependency list
    insert into @dep
    select distinct object_name(parent_object_id), object_name(referenced_object_id), 0, delete_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', delaction as 'DeleteAction' 
    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,'ExecIsDeleteTrigger')=1
    )
        print 'Has "Instead Of Delete" Trigger so parents cannot cascade delete it.
    '

    --1
    select @c1=min(child) from @dep where parent = @p
    while @c1 is not null
    begin
        select @delaction = delaction from @dep where child=@c1 and parent=@p
        if @printdepth >=1 print @char9+@c1+' '+@delaction
        --2
        select @c2=min(child) from @dep where parent = @c1
        while @c2 is not null
        begin
            select @delaction = delaction from @dep where child=@c2 and parent=@c1
            if @printdepth >=2 print @char9+@char9+@c2+' '+@delaction
            --3
            select @c3=min(child) from @dep where parent = @c2
            while @c3 is not null
            begin
                select @delaction = delaction from @dep where child=@c3 and parent=@c2
                if @printdepth >=3 print @char9+@char9+@char9+@c3+' '+@delaction
                --4
                select @c4=min(child) from @dep where parent = @c3
                while @c4 is not null
                begin
                    select @delaction = delaction from @dep where child=@c4 and parent=@c3
                    if @printdepth >=4 print @char9+@char9+@char9+@char9+@c4+' '+@delaction
                    --5
                    select @c5=min(child) from @dep where parent = @c4
                    while @c5 is not null
                    begin
                        select @delaction = delaction from @dep where child=@c5 and parent=@c4
                        if @printdepth >=5 print @char9+@char9+@char9+@char9+@char9+@c5+' '+@delaction
                        --6
                        select @c6=min(child) from @dep where parent = @c5
                        while @c6 is not null
                        begin
                            select @delaction = delaction from @dep where child=@c6 and parent=@c5
                            if @printdepth >=6 print @char9+@char9+@char9+@char9+@char9+@char9+@c6+' '+@delaction
                            --7
                            select @c7=min(child) from @dep where parent = @c6
                            while @c7 is not null
                            begin
                                select @delaction = delaction from @dep where child=@c7 and parent=@c6
                                if @printdepth >=7 print @char9+@char9+@char9+@char9+@char9+@char9+@char9+@c7+' '+@delaction
                                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

    -- Clear the dependency table for the next table
    DELETE FROM @dep;
    
    -- Get the next table
    FETCH NEXT FROM TableCursor INTO @TableName;
END

-- Clean up cursor
CLOSE TableCursor;
DEALLOCATE TableCursor;