-- sample tables with FKs

create table dbo.M (id int primary key, M_c1 int not null, M_c2 datetime not null, M_c3 char(10) not null, M_c4 datetime);

create unique index ux1_A on dbo.M (M_c1);

create unique index ux2_A on dbo.M (M_c2, M_c3);

create table dbo.N1 (id int primary key, N1_c1 int, N1_c2 datetime, N1_c3 char(10), N1_c4 datetime);

create unique index ux1_N1 on dbo.N1 (N1_c1, N1_c3);

create unique index ux2_N1 on dbo.N1 ( N1_c2);

alter table dbo.N1 add constraint FK_N1_M foreign key (N1_c1) references dbo.M (M_c1);

create table dbo.N2 (id int primary key, N2_c1 int, N2_c2 datetime, N2_c3 char(10), N2_c4 datetime);

create unique index ux1_N2 on dbo.N2 (N2_c1);

create unique index ux2_N2 on dbo.N2 (N2_c3, N2_c4);

alter table dbo.N2 add constraint FK1_N2_M foreign key (N2_c2, N2_c3) references dbo.M (M_c2, M_c3);

create table dbo.P1 (id int primary key, P1_c1 int, P1_c2 datetime, P1_c3 char(10), P1_c4 datetime);

create unique index ux1_P1 on dbo.P1 (P1_c1);

alter table dbo.P1 add constraint FK_P1_N1 foreign key (P1_c1, P1_c3) references dbo.N1 (N1_c1, N1_c3);

create table dbo.P2 (id int primary key, P2_c1 int, P2_c2 datetime, P2_c3 char(10), P2_c4 datetime);

create unique index ux1_P2 on dbo.P2 (P2_c1);

create unique index ux2_P2 on dbo.P2 (P2_c2, P2_c3);

alter table dbo.P2 add constraint FK_P2_N1 foreign key (P2_c2) references dbo.N1 (N1_c2);

alter table dbo.P2 add constraint FK_P2_N2 foreign key (P2_c3, P2_c4) references dbo.N2 (N2_c3, N2_c4);

create table dbo.P3 (id int primary key, P3_c1 int, P3_c2 datetime, P3_c3 char(10), P3_c4 datetime);

alter table dbo.P3 add constraint FK_P3_N2 foreign key (P3_c1) references dbo.N2 (id);

create table dbo.Q1 (id int primary key, Q1_c1 int, Q1_c2 datetime, Q1_c3 char(10), Q1_C4 datetime);

alter table dbo.Q1 add constraint FK_Q1_P1 foreign key (Q1_c1) references dbo.P1 (P1_c1);

create table dbo.Q2 (id int primary key, Q2_c1 int, Q2_c2 datetime, Q2_c3 char(10), Q2_c4 datetime);

alter table dbo.Q2 add constraint FK_Q2_P2 foreign key (Q2_c1) references dbo.P2 (id);

create table dbo.Q3 (id int primary key, Q3_c1 int, Q3_c2 datetime, Q3_c3 char(10), Q3_c4 datetime);

alter table dbo.Q3 add constraint FK1_Q3_N2 foreign key (Q3_c1) references dbo.N2 (id);

alter table dbo.Q3 add constraint FK2_Q3_P2 foreign key (Q3_c2, Q3_c3) references dbo.P2 (P2_c2, P2_c3);

GO

-- populate all tables

insert into dbo.M (id, M_c1, M_c2, M_c3, M_c4)

select 1, 10, '2015-01-01', 'AB1', '2015-01-02'

union all

select 2, 20, '2015-01-02', 'AB2', '2015-01-03'

union all

select 3, 30, '2015-01-03', 'AB3', '2015-01-04';

insert into dbo.N1 (id, N1_c1, N1_c2, N1_c3, N1_c4)

select 11, 20, '2015-01-01', 'CD1', '2015-01-02'

union all

select 21, 30, '2015-01-02', 'CD2', '2015-01-03'

union all

select 31, 10, '2015-01-03', 'CD3', '2015-01-04';

insert into dbo.N2 (id, N2_c1, N2_c2, N2_c3, N2_c4)

select 11, 11, '2015-01-01', 'AB1', '2015-01-02'

union all

select 12, 22, '2015-01-02', 'AB2', '2015-01-03'

union all

select 13, 33, '2015-01-03', 'AB3', '2015-01-04';

insert into dbo.P1 (id, P1_c1, P1_c2, P1_c3, P1_c4)

select 100, 20, '2014-01-01', 'CD1', '2015-01-02'

union all

select 101, 30, '2014-01-02', 'CD2', '2015-01-03'

union all

select 102, 10, '2014-01-03', 'CD3', '2015-01-04'

insert into dbo.P2 (id, P2_c1, P2_c2, P2_c3, P2_c4)

select 200, 20, '2015-01-01', 'AB1', '2015-01-02'

union all

select 201, 30, '2015-01-02', 'AB2', '2015-01-03'

union all

select 202, 10, '2015-01-03', 'AB3', '2015-01-04'

insert into dbo.P3 (id, P3_c1, P3_c2, P3_c3, P3_c4)

select 301, 11, '2010-01-01', 'EF1', '2015-02-02'

union all

select 302, 13, '2010-01-02', 'EF2', '2015-02-03'

union all

select 303, 12, '2010-01-03', 'EF3', '2015-02-04'

use SW_TEST

go

if object_id('dbo.usp_searchFK', 'P') is not null

    drop proc dbo.usp_SearchFK;

go

create proc dbo.usp_SearchFK

@table varchar(256) -- use two part name convention

, @lvl int=0 -- do not change

, @ParentTable varchar(256)='' -- do not change

, @debug bit = 1

as

begin

    set nocount on;

    declare @dbg bit;

    set @dbg=@debug;

    if object_id('tempdb..#tbl', 'U') is null

        create table #tbl (id int identity, tablename varchar(256), lvl int, ParentTable varchar(256));

    declare @curS cursor;

    if @lvl = 0

        insert into #tbl (tablename, lvl, ParentTable)

        select @table, @lvl, Null;

    else

        insert into #tbl (tablename, lvl, ParentTable)

        select @table, @lvl,@ParentTable;

    if @dbg=1    

        print replicate('----', @lvl) + 'lvl ' + cast(@lvl as varchar(10)) + ' = ' + @table;

    if not exists (select * from sys.foreign_keys where referenced_object_id = object_id(@table))

        return;

    else

    begin -- else

        set @ParentTable = @table;

        set @curS = cursor for

        select tablename=object_schema_name(parent_object_id)+'.'+object_name(parent_object_id)

        from sys.foreign_keys

        where referenced_object_id = object_id(@table)

        and parent_object_id <> referenced_object_id; -- add this to prevent self-referencing which can create a indefinitive loop;

        open @curS;

        fetch next from @curS into @table;

        while @@fetch_status = 0

        begin --while

            set @lvl = @lvl+1;

            -- recursive call

            exec dbo.usp_SearchFK @table, @lvl, @ParentTable, @dbg;

            set @lvl = @lvl-1;

            fetch next from @curS into @table;

        end --while

        close @curS;

        deallocate @curS;

    end -- else

    if @lvl = 0

        select * from #tbl;

    return;

end

go

exec dbo.usp_SearchFK 'dbo.M'

if object_id('tempdb..#tmp') is not null

    drop table #tmp;

create table #tmp (id int, tablename varchar(256), lvl int, ParentTable varchar(256));

insert into #tmp

exec dbo.usp_SearchFK @table='dbo.M', @debug=0;

declare @where varchar(max) ='where M.id=2' -- if @where clause is null or empty, it will delete tables as a whole with the right order

declare @curFK cursor, @fk_object_id int;

declare @sqlcmd varchar(max)='', @crlf char(2)=char(0x0d)+char(0x0a);

declare @child varchar(256), @parent varchar(256), @lvl int, @id int;

declare @i int;

declare @t table (tablename varchar(128));

declare @curT cursor;

if isnull(@where, '')= ''

begin

    set @curT = cursor for select tablename, lvl from #tmp order by lvl desc

    open @curT;

    fetch next from @curT into @child, @lvl;

    while @@fetch_status = 0

    begin -- loop @curT

        if not exists (select 1 from @t where tablename=@child)

            insert into @t (tablename) values (@child);

        fetch next from @curT into @child, @lvl;

    end -- loop @curT

    close @curT;

    deallocate @curT;

    select @sqlcmd = @sqlcmd + 'delete from ' + tablename + @crlf from @t ;

    print @sqlcmd;

end

else

begin

    declare curT cursor for

    select lvl, id

    from #tmp

    order by lvl desc;

    open curT;

    fetch next from curT into @lvl, @id;

    while @@FETCH_STATUS =0

    begin

        set @i=0;

        if @lvl =0

        begin -- this is the root level

            select @sqlcmd = 'delete from ' + tablename from #tmp where id = @id;

        end -- this is the roolt level

        while @i < @lvl

        begin -- while

            select top 1 @child=TableName, @parent=ParentTable from #tmp where id <= @id-@i and lvl <= @lvl-@i order by lvl desc, id desc;

            set @curFK = cursor for

            select object_id from sys.foreign_keys

            where parent_object_id = object_id(@child)

            and referenced_object_id = object_id(@parent)

            open @curFK;

            fetch next from @curFk into @fk_object_id

            while @@fetch_status =0

            begin -- @curFK

                if @i=0

                    set @sqlcmd = 'delete from ' + @child + @crlf +

                    'from ' + @child + @crlf + 'inner join ' + @parent ;

                else

                    set @sqlcmd = @sqlcmd + @crlf + 'inner join ' + @parent ;

                ;with c as

                (

                    select child = object_schema_name(fc.parent_object_id)+'.' + object_name(fc.parent_object_id), child_col=c.name

                    , parent = object_schema_name(fc.referenced_object_id)+'.' + object_name(fc.referenced_object_id), parent_col=c2.name

                    , rnk = row_number() over (order by (select null))

                    from sys.foreign_key_columns fc

                    inner join sys.columns c

                    on fc.parent_column_id = c.column_id

                    and fc.parent_object_id = c.object_id

                    inner join sys.columns c2

                    on fc.referenced_column_id = c2.column_id

                    and fc.referenced_object_id = c2.object_id

                    where fc.constraint_object_id=@fk_object_id

                )

                    select @sqlcmd =@sqlcmd + case rnk when 1 then ' on ' else ' and ' end

                    + @child +'.'+ child_col +'=' + @parent +'.' + parent_col

                    from c;

                    fetch next from @curFK into @fk_object_id;

            end --@curFK

            close @curFK;

            deallocate @curFK;

            set @i = @i +1;

        end --while

        print @sqlcmd + @crlf + @where + ';';

        print '';

        fetch next from curT into @lvl, @id;

    end

    close curT;

    deallocate curT;

end

go