У меня есть база данных, полная данных клиентов. Он настолько велик, что с ним действительно неудобно работать, и я бы предпочел просто сократить его до 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'
Вот версия принятого ответа, оптимизированная для малонаселенных моделей данных. Он проверяет наличие данных в цепочке 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
Если вы не хотите поддерживать все связанные запросы, предложенные Крисом, ON DELETE CASCADE, безусловно, является самым быстрым и прямым решением. И если вы не хотите, чтобы это было постоянным, почему бы вам не написать код T-SQL, который будет включать и выключать эту опцию, как здесь
удалить исходное ограничение Tbl_A_MyFK (без каскада ON DELETE)
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)
Перейдите в SQL Server Management Studio и щелкните правой кнопкой мыши базу данных. Выберите Задачи->Создать скрипты. Нажмите «Далее» дважды. В окне «Параметры» выберите, чтобы он генерировал только операторы CREATE, и установите для всех значение False, кроме внешних ключей. Нажмите "Далее. Выберите «Таблицы» и снова нажмите «Далее». Нажмите кнопку «Выбрать все» и нажмите «Далее», затем «Готово» и отправьте сценарий в окно запроса или файл по вашему выбору (не используйте буфер обмена, так как это может быть большой сценарий). Теперь удалите весь скрипт, добавляющий таблицы, и у вас должен остаться скрипт для создания внешних ключей.
Сделайте копию этого скрипта, потому что именно так вы восстановите базу данных до ее текущего состояния. Используйте поиск и замену, чтобы добавить ON DELETE CASCADE в конец каждого ограничения. Это может варьироваться в зависимости от того, как в настоящее время настроены ваши FK, и вам может потребоваться выполнить некоторое ручное редактирование.
Повторите генерацию скрипта, но на этот раз установите его для генерации только операторов DROP. Обязательно удалите созданные отбрасывания таблицы вручную. Запустите капли, затем запустите отредактированные создания, чтобы все они каскадировались при удалении. Сделайте свои удаления, снова запустите сценарий удаления, а затем запустите сценарий, который вы сохранили в начале.
Кроме того, СНАЧАЛА СДЕЛАЙТЕ РЕЗЕРВНУЮ КОПИИ СВОЕЙ БД! Даже если это просто база данных для разработчиков, это избавит вас от головной боли, если часть скрипта будет не совсем правильной.
Надеюсь это поможет!
Кстати, вам определенно следует провести некоторое тестирование с вашими полными тестовыми данными, как предложил другой автор, но я понимаю, почему вам это может не понадобиться для начальной разработки. Просто не забудьте включить это как часть QA в какой-то момент.
Сообщение Кевина неполное, его 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
Обычно я просто пишу запросы, чтобы удалить записи, которые мне не нужны, и сохраняю их как файл .sql для дальнейшего использования. Псевдокод:
Я предлагаю пойти дальше и написать сценарий, который добавит каскад при удалении к каждому отношению в базе данных при экспорте списка измененных отношений. Затем вы можете отменить процесс и удалить каскадную команду удаления для каждой таблицы в списке.
Лично если вы собираетесь оставить записи в производстве, я бы также оставил их в разработке. В противном случае вы можете написать код, который отлично работает, когда набор записей мал, но время ожидания истекает при столкновении с реальным набором записей.
Но если вы полны решимости сделать это, я бы сначала скопировал поле идентификатора записей, которые вы хотите удалить, из основной таблицы в рабочую таблицу. Затем я возьму каждую связанную таблицу и напишу операцию удаления для этой рабочей таблицы, чтобы удалить только эти записи. Завершите работу с родительской таблицей. Убедитесь, что это написано в сценарии и сохранено, чтобы в следующий раз, когда вы захотите сделать что-то подобное с вашими тестовыми данными, вы могли легко запустить его, не выясняя, какие переработанные таблицы требуют удаления записей из них.
Взяв принятый ответ немного дальше, мне нужно было сделать это для таблиц в разных схемах. Я обновил скрипт, чтобы включить схему в выводимые скрипты удаления.
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'''
Расширение ответа 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. Это создает прямые отношения только с базовой таблицей. Если конечная таблица имеет другую родительскую связь таблицы, удаление завершается ошибкой.
УДАЛИТЬ ИЗ [dbo].[table2] WHERE TableID in (выберите [ID] из [dbo].[table3], где 1=1)
Если table2 имеет родительское отношение table1
