在SQL Server 2005中，是否可以在不设置表属性的情况下进行级联删除？ [英] In SQL Server 2005, can I do a cascade delete without setting the property on my tables?

问题描述

我有一个充满客户数据的数据库。它太大了，使用起来真的很麻烦，我只想将它缩小到10％的客户，这对开发来说是很大的。我有很多桌子，我不想用 ON DELETE CASCADE更改它们，特别是因为这是一次性交易。

I have a database full of customer data. It's so big that it's really cumbersome to operate on, and I'd rather just slim it down to 10% of the customers, which is plenty for development. I have an awful lot of tables and I don't want to alter them all with "ON DELETE CASCADE", especially because this is a one-time deal.

可以我执行的删除操作会遍历所有表，而无需先设置它们吗？如果没有，我最好的选择是什么？

Can I do a delete operation that cascades through all my tables without setting them up first? If not, what is my best option?

推荐答案

结合您的建议和我在网上找到的脚本，我制定了一个程序生成SQL，无论 ON DELETE CASCADE 如何运行，都可以执行级联删除。这可能是浪费时间，但是我写得很好。这样做的好处是，您可以在每行之间放置一个 GO 语句，而不必进行大笔交易。原来是递归程序；

Combining your advice and a script I found online, I made a procedure that will produce SQL you can run to perform a cascaded delete regardless of ON DELETE CASCADE. It was probably a big waste of time, but I had a good time writing it. An advantage of doing it this way is, you can put a GO statement between each line, and it doesn't have to be one big transaction. The original was a recursive procedure; this one unrolls the recursion into a stack table.

create procedure usp_delete_cascade (
    @base_table_name varchar(200), @base_criteria nvarchar(1000)
)
as begin
    -- Adapted from http://www.sqlteam.com/article/performing-a-cascade-delete-in-sql-server-7
    -- Expects the name of a table, and a conditional for selecting rows
    -- within that table that you want deleted.
    -- Produces SQL that, when run, deletes all table rows referencing the ones
    -- you initially selected, cascading into any number of tables,
    -- without the need for "ON DELETE CASCADE".
    -- Does not appear to work with self-referencing tables, but it will
    -- delete everything beneath them.
    -- To make it easy on the server, put a "GO" statement between each line.

    declare @to_delete table (
        id int identity(1, 1) primary key not null,
        criteria nvarchar(1000) not null,
        table_name varchar(200) not null,
        processed bit not null,
        delete_sql varchar(1000)
    )

    insert into @to_delete (criteria, table_name, processed) values (@base_criteria, @base_table_name, 0)

    declare @id int, @criteria nvarchar(1000), @table_name varchar(200)
    while exists(select 1 from @to_delete where processed = 0) begin
        select top 1 @id = id, @criteria = criteria, @table_name = table_name from @to_delete where processed = 0 order by id desc

        insert into @to_delete (criteria, table_name, processed)
            select referencing_column.name + ' in (select [' + referenced_column.name + '] from [' + @table_name +'] where ' + @criteria + ')',
                referencing_table.name,
                0
            from  sys.foreign_key_columns fk
                inner join sys.columns referencing_column on fk.parent_object_id = referencing_column.object_id 
                    and fk.parent_column_id = referencing_column.column_id 
                inner join  sys.columns referenced_column on fk.referenced_object_id = referenced_column.object_id 
                    and fk.referenced_column_id = referenced_column.column_id 
                inner join  sys.objects referencing_table on fk.parent_object_id = referencing_table.object_id 
                inner join  sys.objects referenced_table on fk.referenced_object_id = referenced_table.object_id 
                inner join  sys.objects constraint_object on fk.constraint_object_id = constraint_object.object_id
            where referenced_table.name = @table_name
                and referencing_table.name != referenced_table.name

        update @to_delete set
            processed = 1
        where id = @id
    end

    select 'print ''deleting from ' + table_name + '...''; delete from [' + table_name + '] where ' + criteria from @to_delete order by id desc
end

exec usp_delete_cascade 'root_table_name', 'id = 123'

这篇关于在SQL Server 2005中，是否可以在不设置表属性的情况下进行级联删除？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！