我有一个充满客户数据的数据库.这是如此之大,以至于操作起来非常麻烦,我宁愿将其减少到10%的客户,这对于开发来说是充足的.我有很多表,我不想用"ON DELETE CASCADE"改变它们,特别是因为这是一次性的交易.
我可以执行删除操作,在没有先设置它们的情况下级联所有表吗？如果没有,我最好的选择是什么？
结合您的建议和我在网上找到的脚本,我制作了一个程序,可以生成SQL,无论如何都可以运行以执行级联删除ON DELETE CASCADE.这可能是浪费时间,但我写得很开心.这样做的一个好处是,你可以GO在每一行之间放一个语句,它不一定是一个大事务.原来是递归程序; 这个将递归展开到堆栈表中.
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'
除非您想要维护Chris提出的所有相关查询,否则ON DELETE CASCADE是迄今为止最快捷,最直接的解决方案.如果你不希望它是永久性的,为什么你没有一些T-SQL代码可以像这里一样打开和关闭这个选项
删除原始Tbl_A_MyFK约束(没有ON DELETE CASCADE)
ALTER TABLE Tbl_A DROP CONSTRAINT Tbl_A_MyFK
Tbl_A_MyFK使用ON DELETE CASCADE 设置约束
ALTER TABLE Tbl_A ADD CONSTRAINT Tbl_A_MyFK FOREIGN KEY (MyFK) REFERENCES Tbl_B(Column) ON DELETE CASCADE
在这里你可以删除
DELETE FROM Tbl_A WHERE ...
放弃你的约束 Tbl_A_MyFK
ALTER TABLE Tbl_A DROP CONSTRAINT Tbl_A_MyFK
在Tbl_A_MyFK没有ON DELETE CASCADE的情况下设置约束
ALTER TABLE Tbl_A ADD CONSTRAINT Tbl_A_MyFK FOREIGN KEY (MyFK) REFERENCES (Tbl_B)
这是针对人口稀少的数据模型优化的已接受答案的一个版本.它在将数据添加到删除列表之前检查FK链中是否存在数据.我用它来清理测试数据.
不要在活动的事务数据库中使用它 - 它将持有锁太长时间.
/*
-- ============================================================================
-- Purpose: Performs a cascading hard-delete.
-- Not for use on an active transactional database- it holds locks for too long.
-- (http://stackoverflow.com/questions/116968/in-sql-server-2005-can-i-do-a-cascade-delete-without-setting-the-property-on-my)
-- eg:
exec dbo.hp_Common_Delete 'tblConsumer', 'Surname = ''TestDxOverdueOneReviewWm''', 1
-- ============================================================================
*/
create proc [dbo].[hp_Common_Delete]
(
@TableName sysname,
@Where nvarchar(4000), -- Shouldn't include 'where' keyword, e.g. Surname = 'smith', NOT where Surname = 'smith'
@IsDebug bit = 0
)
as
set nocount on
begin try
-- Prepare tables to store deletion criteria.
-- #tmp_to_delete stores criteria that is tested for results before being added to #to_delete
create table #to_delete
(
id int identity(1, 1) primary key not null,
criteria nvarchar(4000) not null,
table_name sysname not null,
processed bit not null default(0)
)
create table #tmp_to_delete
(
id int primary key identity(1,1),
criteria nvarchar(4000) not null,
table_name sysname not null
)
-- Open a transaction (it'll be a long one- don't use this on production!)
-- We need a transaction around criteria generation because we only
-- retain criteria that has rows in the db, and we don't want that to change under us.
begin tran
-- If the top-level table meets the deletion criteria, add it
declare @Sql nvarchar(4000)
set @Sql = 'if exists(select top(1) * from ' + @TableName + ' where ' + @Where + ')
insert #to_delete (criteria, table_name) values (''' + replace(@Where, '''', '''''') + ''', ''' + @TableName + ''')'
exec (@Sql)
-- Loop over deletion table, walking foreign keys to generate delete targets
declare @id int, @tmp_id int, @criteria nvarchar(4000), @new_criteria nvarchar(4000), @table_name sysname, @new_table_name sysname
while exists(select 1 from #to_delete where processed = 0)
begin
-- Grab table/criteria to work on
select top(1) @id = id,
@criteria = criteria,
@table_name = table_name
from #to_delete
where processed = 0
order by id desc
-- Insert all immediate child tables into a temp table for processing
insert #tmp_to_delete
select referencing_column.name + ' in (select [' + referenced_column.name + '] from [' + @table_name +'] where ' + @criteria + ')',
referencing_table.name
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
-- Loop on child table criteria, and insert them into delete table if they have records in the db
select @tmp_id = max(id) from #tmp_to_delete
while (@tmp_id >= 1)
begin
select @new_criteria = criteria, @new_table_name = table_name from #tmp_to_delete where id = @tmp_id
set @Sql = 'if exists(select top(1) * from ' + @new_table_name + ' where ' + @new_criteria + ')
insert #to_delete (criteria, table_name) values (''' + replace(@new_criteria, '''', '''''') + ''', ''' + @new_table_name + ''')'
exec (@Sql)
set @tmp_id = @tmp_id - 1
end
truncate table #tmp_to_delete
-- Move to next record
update #to_delete
set processed = 1
where id = @id
end
-- We have a list of all tables requiring deletion. Actually delete now.
select @id = max(id) from #to_delete
while (@id >= 1)
begin
select @criteria = criteria, @table_name = table_name from #to_delete where id = @id
set @Sql = 'delete from [' + @table_name + '] where ' + @criteria
if (@IsDebug = 1) print @Sql
exec (@Sql)
-- Next record
set @id = @id - 1
end
commit
end try
begin catch
-- Any error results in a rollback of the entire job
if (@@trancount > 0) rollback
declare @message nvarchar(2047), @errorProcedure nvarchar(126), @errorMessage nvarchar(2048), @errorNumber int, @errorSeverity int, @errorState int, @errorLine int
select @errorProcedure = isnull(error_procedure(), N'hp_Common_Delete'),
@errorMessage = isnull(error_message(), N'hp_Common_Delete unable to determine error message'),
@errorNumber = error_number(), @errorSeverity = error_severity(), @errorState = error_state(), @errorLine = error_line()
-- Prepare error information as it would be output in SQL Mgt Studio
declare @event nvarchar(2047)
select @event = 'Msg ' + isnull(cast(@errorNumber as varchar), 'null') +
', Level ' + isnull(cast(@errorSeverity as varchar), 'null') +
', State ' + isnull(cast(@errorState as varchar), 'null') +
', Procedure ' + isnull(@errorProcedure, 'null') +
', Line ' + isnull(cast(@errorLine as varchar), 'null') +
': ' + isnull(@errorMessage, '@ErrorMessage null')
print @event
-- Re-raise error to ensure admin/job runners understand there was a failure
raiserror(@errorMessage, @errorSeverity, @errorState)
end catch
进入SQL Server Management Studio并右键单击该数据库.选择Tasks-> Generate Scripts.单击下一步两次.在"选项"窗口中,选择将其设置为仅生成CREATE语句,并将所有内容设置为False,但外键除外.点击下一步.选择Tables并再次单击Next.单击"全选"按钮,然后单击"下一步",然后单击"完成"并将脚本发送到您选择的查询窗口或文件(不要使用剪贴板,因为它可能是一个很大的脚本).现在删除添加表的所有脚本,您应该留下一个脚本来创建外键.
制作该脚本的副本,因为它是将数据库还原到当前状态的方式.使用搜索和替换将ON DELETE CASCADE添加到每个约束的末尾.这可能会有所不同,具体取决于您当前设置的FK的方式,您可能需要进行一些手动编辑.
重复脚本生成,但这次将其设置为仅生成DROP语句.务必手动删除生成的表格.运行drop,然后运行已编辑的创建,使它们在删除时全部级联.删除,再次运行删除脚本,然后运行您在开始时保存的脚本.
此外 - 首先备份您的数据库!即使它只是一个开发数据库,如果脚本的一部分不太正确,它也会让你头疼.
希望这可以帮助!
顺便说一句 - 你应该用你的完整测试数据进行一些测试,如另一张海报所示,但我可以看到为什么你可能不需要那些用于初始开发.只是不要忘记在某些时候将其作为质量保证的一部分.
京公网安备 11010802040832号 | 京ICP备19059560号-6 