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语句中的每个线之间，并且它并没有成为一个大交易。 原来是一个递归过程; 这一个解开递归成堆叠表。
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'
除非你想维护所有相关查询所建议的克里斯，在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链数据的存在。 我用它来清理测试数据。
不要在活跃交易使用DB-将持有锁的时间太长了。
/*
-- ============================================================================
-- 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中，并用鼠标右键单击该数据库。 选择任务 - >生成脚本。 单击下一步两次。 在选项窗口中，选择将其设置为仅生成CREATE语句，并把一切都为False除了外键。 点击下一步。 选择表，然后再次单击下一步。 单击“全选”按钮，然后点击下一步，然后完成并发送脚本到你选择一个查询窗口或文件（不使用剪贴板，因为它可能是一个很大的脚本）的。 现在，删除所有，增加了表的脚本，你应该留下一个脚本来创建你的外键。
制作该脚本的副本，因为它是你如何数据库恢复到其当前状态。 使用搜索和替换添加ON DELETE CASCADE到每个约束的结束。 这可能取决于你的FKS目前如何设置各不相同，你可能需要做一些手工编辑。
重复脚本生成，但这次将其设置为仅生成DROP语句。 一定要手动删除所生成的表中删除 。 运行滴，然后运行已编辑的创建，使他们删除所有级联。 做你删除，再次运行该脚本滴，然后运行您在开始保存过的脚本。
此外- 让你的DB先备份！ 即使它只是一个开发数据库，它将如果脚本的一部分，是不完全正确节省你的头痛。
希望这可以帮助！
顺便说一句 - 你绝对应该做一些测试与你的全部试验数据作为另一个海报建议，但我可以看到为什么你可能不需要，对于最初的开发。 只是不要忘了包括如在某些点QA的一部分。
我通常只是手写的查询，删除我不想记录并保存为将来参考.sql文件。 伪代码：
我的建议是先走，并写一个脚本，将在导出修饰关系的列表，在数据库上删除级联添加到每个关系。 然后你就可以逆转这一过程，并在列表中的每个表中删除的删除级联命令。
就个人而言，如果你要离开生产记录，我还要留在他们的发展。 否则，你可能会写的时候，当面对真正的记录记录虽小，但超时工作正常代码。
但是，如果你有决心做到这一点，我会复制你要首先从主表中德国电信的工作表中的记录id字段。 然后，我将采取每个相关表格，写一个删除加入到工作台只删除这些记录。 完成了父表。 确保此IA写一个脚本，保存，所以你想要做类似的事情到您的测试数据，下一次，你可以很容易，而不必弄清楚什么是需要从他们删除的记录reated表运行它。
以接受的答案远一点，我有必要在不同的模式来做到这一点跨表。 我已经更新了脚本，包括在输出删除脚本的架构。
CREATE PROCEDURE usp_delete_cascade (
@base_table_schema varchar(100), @base_table_name varchar(200), @base_criteria nvarchar(1000)
)
as begin
-- 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_schema varchar(100),
table_name varchar(200) not null,
processed bit not null,
delete_sql varchar(1000)
)
insert into @to_delete (criteria, table_schema, table_name, processed) values (@base_criteria, @base_table_schema, @base_table_name, 0)
declare @id int, @criteria nvarchar(1000), @table_name varchar(200), @table_schema varchar(100)
while exists(select 1 from @to_delete where processed = 0) begin
select top 1 @id = id, @criteria = criteria, @table_name = table_name, @table_schema = table_schema from @to_delete where processed = 0 order by id desc
insert into @to_delete (criteria, table_schema, table_name, processed)
select referencing_column.name + ' in (select [' + referenced_column.name + '] from [' + @table_schema + '].[' + @table_name +'] where ' + @criteria + ')',
schematable.name,
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.schemas schematable on referencing_table.schema_id = schematable.schema_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_schema + '].[' + table_name + '] where ' + criteria from @to_delete order by id desc
end
exec usp_delete_cascade 'schema', 'RootTable', 'Id = 123'
exec usp_delete_cascade 'schema', 'RootTable', 'GuidId = ''A7202F84-FA57-4355-B499-1F8718E29058'''
凯文职位是不完整的，他的T-SQL SP只打印命令，执行这些命令，最后末尾添加此之前，
DECLARE @commandText VARCHAR(8000)
DECLARE curDeletes CURSOR FOR
select 'delete from [' + table_name + '] where ' + criteria from @to_delete order by id desc
OPEN curDeletes
FETCH NEXT FROM curDeletes
INTO
@commandText
WHILE(@@FETCH_STATUS=0)
BEGIN
EXEC (@commandText)
FETCH NEXT FROM curDeletes INTO @commandText
END
CLOSE curDeletes
DEALLOCATE curDeletes
croisharp的回答扩张采取触发器考虑，即感知模式的解决方案，禁止所有影响触发器，删除行，并启用触发器。
CREATE PROCEDURE usp_delete_cascade (
@base_table_schema varchar(100),
@base_table_name varchar(200),
@base_criteria nvarchar(1000)
)
as begin
-- 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_schema varchar(100),
table_name varchar(200) not null,
processed bit not null,
delete_sql varchar(1000)
)
insert into @to_delete (criteria, table_schema, table_name, processed) values (@base_criteria, @base_table_schema, @base_table_name, 0)
declare @id int, @criteria nvarchar(1000), @table_name varchar(200), @table_schema varchar(100)
while exists(select 1 from @to_delete where processed = 0) begin
select top 1 @id = id, @criteria = criteria, @table_name = table_name, @table_schema = table_schema from @to_delete where processed = 0 order by id desc
insert into @to_delete (criteria, table_schema, table_name, processed)
select referencing_column.name + ' in (select [' + referenced_column.name + '] from [' + @table_schema + '].[' + @table_name +'] where ' + @criteria + ')',
schematable.name,
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.schemas schematable on referencing_table.schema_id = schematable.schema_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_schema + '].[' + table_name + '] where ' + criteria from @to_delete order by id desc
DECLARE @commandText VARCHAR(8000), @triggerOn VARCHAR(8000), @triggerOff VARCHAR(8000)
DECLARE curDeletes CURSOR FOR
select
'DELETE FROM [' + table_schema + '].[' + table_name + '] WHERE ' + criteria,
'ALTER TABLE [' + table_schema + '].[' + table_name + '] DISABLE TRIGGER ALL',
'ALTER TABLE [' + table_schema + '].[' + table_name + '] ENABLE TRIGGER ALL'
from @to_delete order by id desc
OPEN curDeletes
FETCH NEXT FROM curDeletes INTO @commandText, @triggerOff, @triggerOn
WHILE(@@FETCH_STATUS=0)
BEGIN
EXEC (@triggerOff)
EXEC (@commandText)
EXEC (@triggerOn)
FETCH NEXT FROM curDeletes INTO @commandText, @triggerOff, @triggerOn
END
CLOSE curDeletes
DEALLOCATE curDeletes
end
后选择你必须建立并执行实际删除
declare @deleteSql nvarchar(1200)
declare delete_cursor cursor for
select table_name, criteria
from @to_delete
order by id desc
open delete_cursor
fetch next from delete_cursor
into @table_name, @criteria
while @@fetch_status = 0
begin
select @deleteSql = 'delete from ' + @table_name + ' where ' + @criteria
--print @deleteSql
-- exec sp_execute @deleteSql
EXEC SP_EXECUTESQL @deleteSql
fetch next from delete_cursor
into @table_name, @criteria
end
close delete_cursor
deallocate delete_cursor
在这里发布一个脚本，将工作与外键包含多个列。
create procedure usp_delete_cascade (
@TableName varchar(200), @Where nvarchar(1000)
) as begin
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 default(0),
delete_sql varchar(1000)
)
DECLARE @MyCursor CURSOR
declare @referencing_column_name varchar(1000)
declare @referencing_table_name varchar(1000)
declare @Sql nvarchar(4000)
insert into @to_delete (criteria, table_name) values ('', @TableName)
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
SET @MyCursor = CURSOR FAST_FORWARD
FOR
select referencing_column.name as column_name,
referencing_table.name as 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
OPEN @MyCursor
FETCH NEXT FROM @MYCursor
INTO @referencing_column_name, @referencing_table_name
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @referencing_column_name
PRINT @referencing_table_name
update @to_delete set criteria = criteria + ' AND '+@table_name+'.'+@referencing_column_name+'='+ @referencing_table_name+'.'+@referencing_column_name
where table_name = @referencing_table_name
if(@@ROWCOUNT = 0)
BEGIN
--if(@id <> 1)
--BEGIN
insert into @to_delete (criteria, table_name)
VALUES( ' LEFT JOIN '+@table_name+' ON '+@table_name+'.'+@referencing_column_name+'='+ @referencing_table_name+'.'+@referencing_column_name+ @criteria,
@referencing_table_name
)
--END
--ELSE
--BEGIN
--insert into @to_delete (criteria, table_name)
--VALUES( ' LEFT JOIN '+@table_name+' ON '+@table_name+'.'+@referencing_column_name+'='+ @referencing_table_name+'.'+@referencing_column_name,
--@referencing_table_name
--)
--END
END
FETCH NEXT FROM @MYCursor
INTO @referencing_column_name, @referencing_table_name
END
CLOSE @MyCursor
DEALLOCATE @MyCursor
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
--select id, table_name, criteria, @Where from @to_delete order by id desc
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 [' + @table_name + '] from [' + @table_name + '] ' + @criteria+' WHERE '+@Where
exec (@Sql)
PRINT @Sql
-- Next record
set @id = @id - 1
end
end
这个脚本有两个问题：1。你必须指明的条件1 = 1，以删除所有表的基础。 2.这将创建仅基表直接的关系。 如果最终表有另一台父的关系时，将删除失败
DELETE FROM [DBO]。[表2] WHERE表格ID中（选择[ID]从[DBO]。[表3]，其中1 = 1）
如果表2有一个父关系表1
