CODEWITHSUNDEEP
sqlsql-server-2008
Fred Thomas
Fred Thomas

Reputation: 944

Calculate the order of deletes SQL Server

I have a huge database that has lots and lots of poorly documented constraints. I need to write some SQL to empty the data out of a subset of these tables, and I am not sure how to determine the order for the deletes. Obviously I need to delete the FK tables before the corresponding PK table, but as I say the relationships are tangled, and poorly documented.

Is there a tool available that will let me determine a correct order for my delete statements?

(I looked at Red Gate tools, and was absolutely amazed to find out they do not support SQL Server 2008R2 which is what I am using.)

Upvotes: 3

Views: 446

Answers (2)

DavidC
DavidC

Reputation: 704

Just pasting the script from Fred Thomas' blog here for future reference. It worked well for me.

select cast(f.name  as varchar(255)) as foreign_key_name
    , r.keycnt
    , cast(c.name as  varchar(255)) as foreign_table
    , cast(fc.name as varchar(255)) as  foreign_column_1
    , cast(fc2.name as varchar(255)) as foreign_column_2
    ,  cast(p.name as varchar(255)) as primary_table
    , cast(rc.name as varchar(255))  as primary_column_1
    , cast(rc2.name as varchar(255)) as  primary_column_2
from sysobjects f
inner join sysobjects c on  f.parent_obj = c.id
inner join sysreferences r on f.id =  r.constid
inner join sysobjects p on r.rkeyid = p.id
inner  join syscolumns rc on r.rkeyid = rc.id and r.rkey1 = rc.colid
inner  join syscolumns fc on r.fkeyid = fc.id and r.fkey1 = fc.colid
left join  syscolumns rc2 on r.rkeyid = rc2.id and r.rkey2 = rc.colid
left join  syscolumns fc2 on r.fkeyid = fc2.id and r.fkey2 = fc.colid
where f.type =  'F'
ORDER BY cast(p.name as varchar(255))

Upvotes: 0

DavidEG
DavidEG

Reputation: 5957

I think you could build a procedure to get the order. Here is an idea:

CREATE PROCEDURE get_delete_order
    @tablename varchar(MAX)
as

BEGIN
    -- Get directed related tables
    SELECT base.name base_name
         , related.name rel_name
      into #RELATED_TABLES
      FROM sys.sysobjects base 
      left join sys.sysforeignkeys on fkeyid = base.id
      left join sys.sysobjects related on related.id = rkeyid
                                      and related.id <> base.id
                                      and related.xtype = 'U'
    WHERE base.xtype = 'U'
      and base.name = @tablename

    -- Get indirected related tables using recursive call
    CREATE #ALL_RELATED_TABLES (rel_name varchar(max), del_order int)
    INSERT INTO #ALL_RELATED_TABLES (rel_name, del_order)
        SELECT rel_name
             , -1
          FROM #RELATED_TABLES

    DECLARE @relate_table
    DECLARE IND_REL CURSOR FOR
        SELECT rel_Name
          FROM #RELATED_TABLES
    open IND_REL
    fetch next from IND_REL into @relate_table

    -- TODO: IMPORTANT!!! Avoid infinite loop here
    while (@@FETCH_STATUS = 0)
    begin
        INSERT INTO #ALL_RELATED_TABLES (rel_name, del_order)
            exec get_delete_order(@relate_table)
        fetch next from IND_REL into @relate_table
    end
    close IND_REL
    deallocate IND_REL

    -- Return resultset
      SELECT rel_name
           , del_order + 1
        FROM #ALL_RELATED_TABLES
    ORDER BY del_order DESC
END

It is untested, just an idea. Real procedure would be more complicated :)

Upvotes: 1

Related Questions