sql - 计算删除SQL Server的顺序

Question

我有一个庞大的数据库，其中包含很多文件记载不足的约束。
我需要编写一些SQL来清空这些表的子集中的数据，而且我不确定如何确定删除的顺序。显然，我需要在对应的PK表之前删除FK表，但是正如我所说的那样，这种关系很纠结，而且文献记载很差。

有没有可用的工具可以让我确定删除语句的正确顺序？

（我看着Red Gate工具，发现它们不支持我正在使用的SQL Server 2008R2感到非常惊讶。）

Answer 1

我认为您可以建立一个程序来获取订单。这是一个主意：

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

它未经测试，只是一个想法。
实际过程会更复杂:)