Не удается усечь таблицу, поскольку на нее ссылается ограничение FOREIGN KEY?
Используя MSSQL2005, можно ли обрезать таблицу с ограничением внешнего ключа, если я сначала усекаю дочернюю таблицу (таблицу с первичным ключом отношения FK)?
Я знаю, что я могу либо
- Использовать
DELETEбез оговорки, а затемRESEEDличность (или) - Удалите FK, обрежьте таблицу и воссоздайте FK.
Я думал, что, пока я обрезаю дочернюю таблицу до родительской, я буду в порядке, не выполняя ни один из указанных выше вариантов, но я получаю эту ошибку:
Невозможно усечь таблицу "TableName", так как на нее ссылается ограничение FOREIGN KEY.
32 ответа
Решение
Правильный; Вы не можете обрезать таблицу с ограничением FK.
Обычно мой процесс для этого:
- Отбрось ограничения
- Усечь стол
- Воссоздайте ограничения.
(Все в сделке, конечно.)
Конечно, это применимо только в том случае, если ребенок уже был усечен. В противном случае я иду другим путем, полностью завися от того, как выглядят мои данные. (Слишком много переменных, чтобы попасть сюда.)
Оригинальный плакат определил, ПОЧЕМУ это так; см. этот ответ для более подробной информации.
DELETE FROM TABLENAME
DBCC CHECKIDENT ('DATABASENAME.dbo.TABLENAME',RESEED, 0)
Обратите внимание, что это, вероятно, не то, что вы хотели бы, если у вас есть миллионы + записей, поскольку это очень медленно.
Так как TRUNCATE TABLE является командой DDL, она не может проверить, ссылаются ли записи в таблице на запись в дочерней таблице.
Вот почему DELETE работает и TRUNCATE TABLE нет: потому что база данных может убедиться, что на нее не ссылается другая запись.
Без ALTER TABLE
-- Delete all records
DELETE FROM [TableName]
-- Set current ID to "1"
-- If table already contains data, use "0"
-- If table is empty and never insert data, use "1"
-- Use SP https://github.com/reduardo7/TableTruncate
DBCC CHECKIDENT ([TableName], RESEED, [0|1])
Как хранимая процедура
https://github.com/reduardo7/TableTruncate
Обратите внимание, что это, вероятно, не то, что вы хотели бы, если у вас есть миллионы + записей, поскольку это очень медленно.
Решение @denver_citizen, представленное выше, не работает для меня, но мне понравился его дух, поэтому я изменил несколько вещей:
- сделал это хранимой процедурой
- изменил способ заполнения и воссоздания внешних ключей
- исходный сценарий усекает все ссылочные таблицы, это может вызвать ошибку нарушения внешнего ключа, когда на ссылочную таблицу есть другие ссылки на внешний ключ. Этот скрипт усекает только таблицу, указанную в качестве параметра. Пользователь сам может несколько раз вызвать эту хранимую процедуру для всех таблиц в правильном порядке.
Для блага публики вот обновленный скрипт:
CREATE PROCEDURE [dbo].[truncate_non_empty_table]
@TableToTruncate VARCHAR(64)
AS
BEGIN
SET NOCOUNT ON
-- GLOBAL VARIABLES
DECLARE @i int
DECLARE @Debug bit
DECLARE @Recycle bit
DECLARE @Verbose bit
DECLARE @TableName varchar(80)
DECLARE @ColumnName varchar(80)
DECLARE @ReferencedTableName varchar(80)
DECLARE @ReferencedColumnName varchar(80)
DECLARE @ConstraintName varchar(250)
DECLARE @CreateStatement varchar(max)
DECLARE @DropStatement varchar(max)
DECLARE @TruncateStatement varchar(max)
DECLARE @CreateStatementTemp varchar(max)
DECLARE @DropStatementTemp varchar(max)
DECLARE @TruncateStatementTemp varchar(max)
DECLARE @Statement varchar(max)
-- 1 = Will not execute statements
SET @Debug = 0
-- 0 = Will not create or truncate storage table
-- 1 = Will create or truncate storage table
SET @Recycle = 0
-- 1 = Will print a message on every step
set @Verbose = 1
SET @i = 1
SET @CreateStatement = 'ALTER TABLE [dbo].[<tablename>] WITH NOCHECK ADD CONSTRAINT [<constraintname>] FOREIGN KEY([<column>]) REFERENCES [dbo].[<reftable>] ([<refcolumn>])'
SET @DropStatement = 'ALTER TABLE [dbo].[<tablename>] DROP CONSTRAINT [<constraintname>]'
SET @TruncateStatement = 'TRUNCATE TABLE [<tablename>]'
-- Drop Temporary tables
IF OBJECT_ID('tempdb..#FKs') IS NOT NULL
DROP TABLE #FKs
-- GET FKs
SELECT ROW_NUMBER() OVER (ORDER BY OBJECT_NAME(parent_object_id), clm1.name) as ID,
OBJECT_NAME(constraint_object_id) as ConstraintName,
OBJECT_NAME(parent_object_id) as TableName,
clm1.name as ColumnName,
OBJECT_NAME(referenced_object_id) as ReferencedTableName,
clm2.name as ReferencedColumnName
INTO #FKs
FROM sys.foreign_key_columns fk
JOIN sys.columns clm1
ON fk.parent_column_id = clm1.column_id
AND fk.parent_object_id = clm1.object_id
JOIN sys.columns clm2
ON fk.referenced_column_id = clm2.column_id
AND fk.referenced_object_id= clm2.object_id
--WHERE OBJECT_NAME(parent_object_id) not in ('//tables that you do not wont to be truncated')
WHERE OBJECT_NAME(referenced_object_id) = @TableToTruncate
ORDER BY OBJECT_NAME(parent_object_id)
-- Prepare Storage Table
IF Not EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Internal_FK_Definition_Storage')
BEGIN
IF @Verbose = 1
PRINT '1. Creating Process Specific Tables...'
-- CREATE STORAGE TABLE IF IT DOES NOT EXISTS
CREATE TABLE [Internal_FK_Definition_Storage]
(
ID int not null identity(1,1) primary key,
FK_Name varchar(250) not null,
FK_CreationStatement varchar(max) not null,
FK_DestructionStatement varchar(max) not null,
Table_TruncationStatement varchar(max) not null
)
END
ELSE
BEGIN
IF @Recycle = 0
BEGIN
IF @Verbose = 1
PRINT '1. Truncating Process Specific Tables...'
-- TRUNCATE TABLE IF IT ALREADY EXISTS
TRUNCATE TABLE [Internal_FK_Definition_Storage]
END
ELSE
PRINT '1. Process specific table will be recycled from previous execution...'
END
IF @Recycle = 0
BEGIN
IF @Verbose = 1
PRINT '2. Backing up Foreign Key Definitions...'
-- Fetch and persist FKs
WHILE (@i <= (SELECT MAX(ID) FROM #FKs))
BEGIN
SET @ConstraintName = (SELECT ConstraintName FROM #FKs WHERE ID = @i)
SET @TableName = (SELECT TableName FROM #FKs WHERE ID = @i)
SET @ColumnName = (SELECT ColumnName FROM #FKs WHERE ID = @i)
SET @ReferencedTableName = (SELECT ReferencedTableName FROM #FKs WHERE ID = @i)
SET @ReferencedColumnName = (SELECT ReferencedColumnName FROM #FKs WHERE ID = @i)
SET @DropStatementTemp = REPLACE(REPLACE(@DropStatement,'<tablename>',@TableName),'<constraintname>',@ConstraintName)
SET @CreateStatementTemp = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@CreateStatement,'<tablename>',@TableName),'<column>',@ColumnName),'<constraintname>',@ConstraintName),'<reftable>',@ReferencedTableName),'<refcolumn>',@ReferencedColumnName)
SET @TruncateStatementTemp = REPLACE(@TruncateStatement,'<tablename>',@TableName)
INSERT INTO [Internal_FK_Definition_Storage]
SELECT @ConstraintName, @CreateStatementTemp, @DropStatementTemp, @TruncateStatementTemp
SET @i = @i + 1
IF @Verbose = 1
PRINT ' > Backing up [' + @ConstraintName + '] from [' + @TableName + ']'
END
END
ELSE
PRINT '2. Backup up was recycled from previous execution...'
IF @Verbose = 1
PRINT '3. Dropping Foreign Keys...'
-- DROP FOREING KEYS
SET @i = 1
WHILE (@i <= (SELECT MAX(ID) FROM [Internal_FK_Definition_Storage]))
BEGIN
SET @ConstraintName = (SELECT FK_Name FROM [Internal_FK_Definition_Storage] WHERE ID = @i)
SET @Statement = (SELECT FK_DestructionStatement FROM [Internal_FK_Definition_Storage] WITH (NOLOCK) WHERE ID = @i)
IF @Debug = 1
PRINT @Statement
ELSE
EXEC(@Statement)
SET @i = @i + 1
IF @Verbose = 1
PRINT ' > Dropping [' + @ConstraintName + ']'
END
IF @Verbose = 1
PRINT '4. Truncating Tables...'
-- TRUNCATE TABLES
-- SzP: commented out as the tables to be truncated might also contain tables that has foreign keys
-- to resolve this the stored procedure should be called recursively, but I dont have the time to do it...
/*
SET @i = 1
WHILE (@i <= (SELECT MAX(ID) FROM [Internal_FK_Definition_Storage]))
BEGIN
SET @Statement = (SELECT Table_TruncationStatement FROM [Internal_FK_Definition_Storage] WHERE ID = @i)
IF @Debug = 1
PRINT @Statement
ELSE
EXEC(@Statement)
SET @i = @i + 1
IF @Verbose = 1
PRINT ' > ' + @Statement
END
*/
IF @Verbose = 1
PRINT ' > TRUNCATE TABLE [' + @TableToTruncate + ']'
IF @Debug = 1
PRINT 'TRUNCATE TABLE [' + @TableToTruncate + ']'
ELSE
EXEC('TRUNCATE TABLE [' + @TableToTruncate + ']')
IF @Verbose = 1
PRINT '5. Re-creating Foreign Keys...'
-- CREATE FOREING KEYS
SET @i = 1
WHILE (@i <= (SELECT MAX(ID) FROM [Internal_FK_Definition_Storage]))
BEGIN
SET @ConstraintName = (SELECT FK_Name FROM [Internal_FK_Definition_Storage] WHERE ID = @i)
SET @Statement = (SELECT FK_CreationStatement FROM [Internal_FK_Definition_Storage] WHERE ID = @i)
IF @Debug = 1
PRINT @Statement
ELSE
EXEC(@Statement)
SET @i = @i + 1
IF @Verbose = 1
PRINT ' > Re-creating [' + @ConstraintName + ']'
END
IF @Verbose = 1
PRINT '6. Process Completed'
END
Ну, так как я не нашел примеров очень простого решения, которое я использовал, а именно:
- Сбросить внешний ключ;
- Усеченный стол
- Воссоздать внешний ключ
Здесь это идет:
1) Найдите имя внешнего ключа, который вызывает ошибку (например: FK_PROBLEM_REASON, с полем IDиз таблицы TABLE_OWNING_CONSTRAINT)
2) Удалить этот ключ из таблицы:
ALTER TABLE TABLE_OWNING_CONSTRAINT DROP CONSTRAINT FK_PROBLEM_REASON
3) Обрезать требуемый стол
TRUNCATE TABLE TABLE_TO_TRUNCATE
4) Повторно добавьте ключ к этой первой таблице:
ALTER TABLE TABLE_OWNING_CONSTRAINT ADD CONSTRAINT FK_PROBLEM_REASON FOREIGN KEY(ID) REFERENCES TABLE_TO_TRUNCATE (ID)
Вот и все.
Используйте следующую команду после удаления всех строк в этой таблице с помощью оператора delete
delete from tablename
DBCC CHECKIDENT ('tablename', RESEED, 0)
РЕДАКТИРОВАТЬ: исправлен синтаксис для SQL Server
SET FOREIGN_KEY_CHECKS = 0;
truncate table "yourTableName";
SET FOREIGN_KEY_CHECKS = 1;
Вы можете выполнить этот шаг, reseeding table Вы можете удалить данные таблицы.
delete from table_name
dbcc checkident('table_name',reseed,0)
если возникнет какая-то ошибка, вам придется заново заполнить основную таблицу.
Вот сценарий, который я написал, чтобы автоматизировать процесс. Я надеюсь, что это помогает.
SET NOCOUNT ON
-- GLOBAL VARIABLES
DECLARE @i int
DECLARE @Debug bit
DECLARE @Recycle bit
DECLARE @Verbose bit
DECLARE @TableName varchar(80)
DECLARE @ColumnName varchar(80)
DECLARE @ReferencedTableName varchar(80)
DECLARE @ReferencedColumnName varchar(80)
DECLARE @ConstraintName varchar(250)
DECLARE @CreateStatement varchar(max)
DECLARE @DropStatement varchar(max)
DECLARE @TruncateStatement varchar(max)
DECLARE @CreateStatementTemp varchar(max)
DECLARE @DropStatementTemp varchar(max)
DECLARE @TruncateStatementTemp varchar(max)
DECLARE @Statement varchar(max)
-- 1 = Will not execute statements
SET @Debug = 0
-- 0 = Will not create or truncate storage table
-- 1 = Will create or truncate storage table
SET @Recycle = 0
-- 1 = Will print a message on every step
set @Verbose = 1
SET @i = 1
SET @CreateStatement = 'ALTER TABLE [dbo].[<tablename>] WITH NOCHECK ADD CONSTRAINT [<constraintname>] FOREIGN KEY([<column>]) REFERENCES [dbo].[<reftable>] ([<refcolumn>])'
SET @DropStatement = 'ALTER TABLE [dbo].[<tablename>] DROP CONSTRAINT [<constraintname>]'
SET @TruncateStatement = 'TRUNCATE TABLE [<tablename>]'
-- Drop Temporary tables
DROP TABLE #FKs
-- GET FKs
SELECT ROW_NUMBER() OVER (ORDER BY OBJECT_NAME(parent_object_id), clm1.name) as ID,
OBJECT_NAME(constraint_object_id) as ConstraintName,
OBJECT_NAME(parent_object_id) as TableName,
clm1.name as ColumnName,
OBJECT_NAME(referenced_object_id) as ReferencedTableName,
clm2.name as ReferencedColumnName
INTO #FKs
FROM sys.foreign_key_columns fk
JOIN sys.columns clm1
ON fk.parent_column_id = clm1.column_id
AND fk.parent_object_id = clm1.object_id
JOIN sys.columns clm2
ON fk.referenced_column_id = clm2.column_id
AND fk.referenced_object_id= clm2.object_id
WHERE OBJECT_NAME(parent_object_id) not in ('//tables that you do not wont to be truncated')
ORDER BY OBJECT_NAME(parent_object_id)
-- Prepare Storage Table
IF Not EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Internal_FK_Definition_Storage')
BEGIN
IF @Verbose = 1
PRINT '1. Creating Process Specific Tables...'
-- CREATE STORAGE TABLE IF IT DOES NOT EXISTS
CREATE TABLE [Internal_FK_Definition_Storage]
(
ID int not null identity(1,1) primary key,
FK_Name varchar(250) not null,
FK_CreationStatement varchar(max) not null,
FK_DestructionStatement varchar(max) not null,
Table_TruncationStatement varchar(max) not null
)
END
ELSE
BEGIN
IF @Recycle = 0
BEGIN
IF @Verbose = 1
PRINT '1. Truncating Process Specific Tables...'
-- TRUNCATE TABLE IF IT ALREADY EXISTS
TRUNCATE TABLE [Internal_FK_Definition_Storage]
END
ELSE
PRINT '1. Process specific table will be recycled from previous execution...'
END
IF @Recycle = 0
BEGIN
IF @Verbose = 1
PRINT '2. Backing up Foreign Key Definitions...'
-- Fetch and persist FKs
WHILE (@i <= (SELECT MAX(ID) FROM #FKs))
BEGIN
SET @ConstraintName = (SELECT ConstraintName FROM #FKs WHERE ID = @i)
SET @TableName = (SELECT TableName FROM #FKs WHERE ID = @i)
SET @ColumnName = (SELECT ColumnName FROM #FKs WHERE ID = @i)
SET @ReferencedTableName = (SELECT ReferencedTableName FROM #FKs WHERE ID = @i)
SET @ReferencedColumnName = (SELECT ReferencedColumnName FROM #FKs WHERE ID = @i)
SET @DropStatementTemp = REPLACE(REPLACE(@DropStatement,'<tablename>',@TableName),'<constraintname>',@ConstraintName)
SET @CreateStatementTemp = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@CreateStatement,'<tablename>',@TableName),'<column>',@ColumnName),'<constraintname>',@ConstraintName),'<reftable>',@ReferencedTableName),'<refcolumn>',@ReferencedColumnName)
SET @TruncateStatementTemp = REPLACE(@TruncateStatement,'<tablename>',@TableName)
INSERT INTO [Internal_FK_Definition_Storage]
SELECT @ConstraintName, @CreateStatementTemp, @DropStatementTemp, @TruncateStatementTemp
SET @i = @i + 1
IF @Verbose = 1
PRINT ' > Backing up [' + @ConstraintName + '] from [' + @TableName + ']'
END
END
ELSE
PRINT '2. Backup up was recycled from previous execution...'
IF @Verbose = 1
PRINT '3. Dropping Foreign Keys...'
-- DROP FOREING KEYS
SET @i = 1
WHILE (@i <= (SELECT MAX(ID) FROM [Internal_FK_Definition_Storage]))
BEGIN
SET @ConstraintName = (SELECT FK_Name FROM [Internal_FK_Definition_Storage] WHERE ID = @i)
SET @Statement = (SELECT FK_DestructionStatement FROM [Internal_FK_Definition_Storage] WITH (NOLOCK) WHERE ID = @i)
IF @Debug = 1
PRINT @Statement
ELSE
EXEC(@Statement)
SET @i = @i + 1
IF @Verbose = 1
PRINT ' > Dropping [' + @ConstraintName + ']'
END
IF @Verbose = 1
PRINT '4. Truncating Tables...'
-- TRUNCATE TABLES
SET @i = 1
WHILE (@i <= (SELECT MAX(ID) FROM [Internal_FK_Definition_Storage]))
BEGIN
SET @Statement = (SELECT Table_TruncationStatement FROM [Internal_FK_Definition_Storage] WHERE ID = @i)
IF @Debug = 1
PRINT @Statement
ELSE
EXEC(@Statement)
SET @i = @i + 1
IF @Verbose = 1
PRINT ' > ' + @Statement
END
IF @Verbose = 1
PRINT '5. Re-creating Foreign Keys...'
-- CREATE FOREING KEYS
SET @i = 1
WHILE (@i <= (SELECT MAX(ID) FROM [Internal_FK_Definition_Storage]))
BEGIN
SET @ConstraintName = (SELECT FK_Name FROM [Internal_FK_Definition_Storage] WHERE ID = @i)
SET @Statement = (SELECT FK_CreationStatement FROM [Internal_FK_Definition_Storage] WHERE ID = @i)
IF @Debug = 1
PRINT @Statement
ELSE
EXEC(@Statement)
SET @i = @i + 1
IF @Verbose = 1
PRINT ' > Re-creating [' + @ConstraintName + ']'
END
IF @Verbose = 1
PRINT '6. Process Completed'
Ответ @denver_citizen мне не совсем помог, но я изменил его, чтобы учесть:
- Композитные ключи
- Действия при удалении и обновлении
- Проверка индекса при повторном добавлении
- Схемы кроме dbo
- Несколько столов одновременно
DECLARE @Debug bit = 0;
-- List of tables to truncate
select
SchemaName, Name
into #tables
from (values
('schema', 'table')
,('schema2', 'table2')
) as X(SchemaName, Name)
BEGIN TRANSACTION TruncateTrans;
with foreignKeys AS (
SELECT
SCHEMA_NAME(fk.schema_id) as SchemaName
,fk.Name as ConstraintName
,OBJECT_NAME(fk.parent_object_id) as TableName
,OBJECT_NAME(fk.referenced_object_id) as ReferencedTableName
,fc.constraint_column_id
,COL_NAME(fk.parent_object_id, fc.parent_column_id) AS ColumnName
,COL_NAME(fk.referenced_object_id, fc.referenced_column_id) as ReferencedColumnName
,fk.delete_referential_action_desc
,fk.update_referential_action_desc
FROM sys.foreign_keys AS fk
JOIN sys.foreign_key_columns AS fc
ON fk.object_id = fc.constraint_object_id
JOIN #tables tbl
ON SCHEMA_NAME(fk.schema_id) = tbl.SchemaName
AND OBJECT_NAME(fc.referenced_object_id) = tbl.Name
)
select
quotename(fk.ConstraintName) AS ConstraintName
,quotename(fk.SchemaName) + '.' + quotename(fk.TableName) AS TableName
,quotename(fk.SchemaName) + '.' + quotename(fk.ReferencedTableName) AS ReferencedTableName
,replace(fk.delete_referential_action_desc, '_', ' ') AS DeleteAction
,replace(fk.update_referential_action_desc, '_', ' ') AS UpdateAction
,STUFF((
SELECT ',' + quotename(fk2.ColumnName)
FROM foreignKeys fk2
WHERE fk2.ConstraintName = fk.ConstraintName
ORDER BY fk2.constraint_column_id
FOR XML PATH(''),TYPE
).value('.','VARCHAR(MAX)'),1,1,'') AS ColumnNames
,STUFF((
SELECT ',' + quotename(fk2.ReferencedColumnName)
FROM foreignKeys fk2
WHERE fk2.ConstraintName = fk.ConstraintName
ORDER BY fk2.constraint_column_id
FOR XML PATH(''),TYPE
).value('.','VARCHAR(MAX)'),1,1,'') AS ReferencedColumnNames
into #FKs
from foreignKeys fk
GROUP BY fk.SchemaName, fk.ConstraintName, fk.TableName, fk.ReferencedTableName, fk.delete_referential_action_desc, fk.update_referential_action_desc
-- Drop FKs
select
identity(int,1,1) as ID,
'ALTER TABLE ' + fk.TableName + ' DROP CONSTRAINT ' + fk.ConstraintName AS script
into #scripts
from #FKs fk
-- Truncate
insert into #scripts
select distinct
'TRUNCATE TABLE ' + fk.TableName AS script
from #FKs fk
-- Recreate
insert into #scripts
select
'ALTER TABLE ' + fk.TableName +
' WITH CHECK ADD CONSTRAINT ' + fk.ConstraintName +
' FOREIGN KEY ('+ fk.ColumnNames +')' +
' REFERENCES ' + fk.ReferencedTableName +' ('+ fk.ReferencedColumnNames +')' +
' ON DELETE ' + fk.DeleteAction + ' ON UPDATE ' + fk.UpdateAction AS script
from #FKs fk
DECLARE @script nvarchar(MAX);
DECLARE curScripts CURSOR FOR
select script
from #scripts
order by ID
OPEN curScripts
WHILE 1=1 BEGIN
FETCH NEXT FROM curScripts INTO @script
IF @@FETCH_STATUS != 0 BREAK;
print @script;
IF @Debug = 0
EXEC (@script);
END
CLOSE curScripts
DEALLOCATE curScripts
drop table #scripts
drop table #FKs
drop table #tables
COMMIT TRANSACTION TruncateTrans;
Найдено в другом месте в Интернете
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
EXEC sp_MSForEachTable 'ALTER TABLE ? DISABLE TRIGGER ALL'
-- EXEC sp_MSForEachTable 'DELETE FROM ?' -- Uncomment to execute
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
EXEC sp_MSForEachTable 'ALTER TABLE ? ENABLE TRIGGER ALL'
Вы не можете обрезать таблицу, если не отбрасываете ограничения. Отключение также не работает. тебе нужно все бросить. Я сделал скрипт, который отбрасывает все ограничения, а затем воссоздает.
Обязательно заверните это в транзакцию;)
SET NOCOUNT ON
GO
DECLARE @table TABLE(
RowId INT PRIMARY KEY IDENTITY(1, 1),
ForeignKeyConstraintName NVARCHAR(200),
ForeignKeyConstraintTableSchema NVARCHAR(200),
ForeignKeyConstraintTableName NVARCHAR(200),
ForeignKeyConstraintColumnName NVARCHAR(200),
PrimaryKeyConstraintName NVARCHAR(200),
PrimaryKeyConstraintTableSchema NVARCHAR(200),
PrimaryKeyConstraintTableName NVARCHAR(200),
PrimaryKeyConstraintColumnName NVARCHAR(200)
)
INSERT INTO @table(ForeignKeyConstraintName, ForeignKeyConstraintTableSchema, ForeignKeyConstraintTableName, ForeignKeyConstraintColumnName)
SELECT
U.CONSTRAINT_NAME,
U.TABLE_SCHEMA,
U.TABLE_NAME,
U.COLUMN_NAME
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE U
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C
ON U.CONSTRAINT_NAME = C.CONSTRAINT_NAME
WHERE
C.CONSTRAINT_TYPE = 'FOREIGN KEY'
UPDATE @table SET
PrimaryKeyConstraintName = UNIQUE_CONSTRAINT_NAME
FROM
@table T
INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS R
ON T.ForeignKeyConstraintName = R.CONSTRAINT_NAME
UPDATE @table SET
PrimaryKeyConstraintTableSchema = TABLE_SCHEMA,
PrimaryKeyConstraintTableName = TABLE_NAME
FROM @table T
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C
ON T.PrimaryKeyConstraintName = C.CONSTRAINT_NAME
UPDATE @table SET
PrimaryKeyConstraintColumnName = COLUMN_NAME
FROM @table T
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE U
ON T.PrimaryKeyConstraintName = U.CONSTRAINT_NAME
--DROP CONSTRAINT:
DECLARE @dynSQL varchar(MAX);
DECLARE cur CURSOR FOR
SELECT
'
ALTER TABLE [' + ForeignKeyConstraintTableSchema + '].[' + ForeignKeyConstraintTableName + ']
DROP CONSTRAINT ' + ForeignKeyConstraintName + '
'
FROM
@table
OPEN cur
FETCH cur into @dynSQL
WHILE @@FETCH_STATUS = 0
BEGIN
exec(@dynSQL)
print @dynSQL
FETCH cur into @dynSQL
END
CLOSE cur
DEALLOCATE cur
---------------------
--HERE GOES YOUR TRUNCATES!!!!!
--HERE GOES YOUR TRUNCATES!!!!!
--HERE GOES YOUR TRUNCATES!!!!!
truncate table your_table
--HERE GOES YOUR TRUNCATES!!!!!
--HERE GOES YOUR TRUNCATES!!!!!
--HERE GOES YOUR TRUNCATES!!!!!
---------------------
--ADD CONSTRAINT:
DECLARE cur2 CURSOR FOR
SELECT
'
ALTER TABLE [' + ForeignKeyConstraintTableSchema + '].[' + ForeignKeyConstraintTableName + ']
ADD CONSTRAINT ' + ForeignKeyConstraintName + ' FOREIGN KEY(' + ForeignKeyConstraintColumnName + ') REFERENCES [' + PrimaryKeyConstraintTableSchema + '].[' + PrimaryKeyConstraintTableName + '](' + PrimaryKeyConstraintColumnName + ')
'
FROM
@table
OPEN cur2
FETCH cur2 into @dynSQL
WHILE @@FETCH_STATUS = 0
BEGIN
exec(@dynSQL)
print @dynSQL
FETCH cur2 into @dynSQL
END
CLOSE cur2
DEALLOCATE cur2
Если я правильно понимаю, вам нужно иметь чистую среду для БД, включающую интеграционные тесты.
Мой подход здесь состоит в том, чтобы отбросить всю схему и воссоздать ее позже.
Причины:
- У вас, вероятно, уже есть сценарий "создания схемы". Повторно использовать его для изоляции теста легко.
- Создание схемы довольно быстро.
- При таком подходе довольно легко настроить сценарий так, чтобы каждый прибор создавал НОВУЮ схему (с временным именем), а затем вы можете запустить параллельный запуск тестовых приспособлений, что значительно ускоряет работу самой медленной части вашего набора тестов.,
Я пишу следующие способы и попытался параметризовать их, чтобы вы могли запустить их в Query document Или сделать полезное SPс ними легко.
А) Удалить
Если в вашей таблице нет миллионов записей, это хорошо работает ине имеет команд Alter:
---------------------------------------------------------------
------------------- Just Fill Parameters Value ----------------
---------------------------------------------------------------
DECLARE @DbName AS NVARCHAR(30) = 'MyDb' --< Db Name
DECLARE @Schema AS NVARCHAR(30) = 'dbo' --< Schema
DECLARE @TableName AS NVARCHAR(30) = 'Book' --< Table Name
------------------ /Just Fill Parameters Value ----------------
DECLARE @Query AS NVARCHAR(500) = 'Delete FROM ' + @TableName
EXECUTE sp_executesql @Query
SET @Query=@DbName+'.'+@Schema+'.'+@TableName
DBCC CHECKIDENT (@Query,RESEED, 0)
- В моем ответе выше метод решения упомянутой проблемы в вопросе основан на ответе@s15199d.
Б) усекать
Если в вашей таблице миллионы записей или у вас нет проблем скомандой Alter в ваших кодах, используйте эту:
-- Book Student
--
-- | BookId | Field1 | | StudentId | BookId |
-- --------------------- ------------------------
-- | 1 | A | | 2 | 1 |
-- | 2 | B | | 1 | 1 |
-- | 3 | C | | 2 | 3 |
---------------------------------------------------------------
------------------- Just Fill Parameters Value ----------------
---------------------------------------------------------------
DECLARE @DbName AS NVARCHAR(30) = 'MyDb'
DECLARE @Schema AS NVARCHAR(30) = 'dbo'
DECLARE @TableName_ToTruncate AS NVARCHAR(30) = 'Book'
DECLARE @TableName_OfOwnerOfConstraint AS NVARCHAR(30) = 'Student' --< Decelations About FK_Book_Constraint
DECLARE @Ref_ColumnName_In_TableName_ToTruncate AS NVARCHAR(30) = 'BookId' --< Decelations About FK_Book_Constraint
DECLARE @FK_ColumnName_In_TableOfOwnerOfConstraint AS NVARCHAR(30) = 'Fk_BookId' --< Decelations About FK_Book_Constraint
DECLARE @FK_ConstraintName AS NVARCHAR(30) = 'FK_Book_Constraint' --< Decelations About FK_Book_Constraint
------------------ /Just Fill Parameters Value ----------------
DECLARE @Query AS NVARCHAR(2000)
SET @Query= 'ALTER TABLE '+@TableName_OfOwnerOfConstraint+' DROP CONSTRAINT '+@FK_ConstraintName
EXECUTE sp_executesql @Query
SET @Query= 'Truncate Table '+ @TableName_ToTruncate
EXECUTE sp_executesql @Query
SET @Query= 'ALTER TABLE '+@TableName_OfOwnerOfConstraint+' ADD CONSTRAINT '+@FK_ConstraintName+' FOREIGN KEY('+@FK_ColumnName_In_TableOfOwnerOfConstraint+') REFERENCES '+@TableName_ToTruncate+'('+@Ref_ColumnName_In_TableName_ToTruncate+')'
EXECUTE sp_executesql @Query
В моем ответе выше метод решения упомянутой проблемы в вопросе основан на ответе@LauroWolffValenteSobrinho.
Если у вас есть более одного ограничения, вы должны добавить его коды, как я, к вышеуказанному запросу
Также вы можете изменить вышеуказанный код базы@SerjSagan, чтобы отключить ограничение включения
truncate не работает для меня, delete + reseed - лучший выход. В случае, если есть некоторые из вас, кому нужно выполнить итерации по огромному количеству таблиц для выполнения удаления + повторного заполнения, вы можете столкнуться с проблемами с некоторыми таблицами, у которых нет столбца идентификаторов, следующий код проверяет, существует ли столбец идентификаторов, прежде чем пытаться пересеивать
EXEC ('DELETE FROM [schemaName].[tableName]')
IF EXISTS (Select * from sys.identity_columns where object_name(object_id) = 'tableName')
BEGIN
EXEC ('DBCC CHECKIDENT ([schemaName.tableName], RESEED, 0)')
END
За MS SQLПо крайней мере, в более новых версиях вы можете просто отключить ограничения с помощью следующего кода:
ALTER TABLE Orders
NOCHECK CONSTRAINT [FK_dbo.Orders_dbo.Customers_Customer_Id]
GO
TRUNCATE TABLE Customers
GO
ALTER TABLE Orders
WITH CHECK CHECK CONSTRAINT [FK_dbo.Orders_dbo.Customers_Customer_Id]
GO
Единственный способ - удалить внешние ключи перед усечением. А после усечения данных вы должны заново создать индексы.
Следующий сценарий генерирует требуемый SQL для снятия всех ограничений внешнего ключа.
DECLARE @drop NVARCHAR(MAX) = N'';
SELECT @drop += N'
ALTER TABLE ' + QUOTENAME(cs.name) + '.' + QUOTENAME(ct.name)
+ ' DROP CONSTRAINT ' + QUOTENAME(fk.name) + ';'
FROM sys.foreign_keys AS fk
INNER JOIN sys.tables AS ct
ON fk.parent_object_id = ct.[object_id]
INNER JOIN sys.schemas AS cs
ON ct.[schema_id] = cs.[schema_id];
SELECT @drop
Затем следующий сценарий генерирует необходимый SQL для воссоздания внешних ключей.
DECLARE @create NVARCHAR(MAX) = N'';
SELECT @create += N'
ALTER TABLE '
+ QUOTENAME(cs.name) + '.' + QUOTENAME(ct.name)
+ ' ADD CONSTRAINT ' + QUOTENAME(fk.name)
+ ' FOREIGN KEY (' + STUFF((SELECT ',' + QUOTENAME(c.name)
-- get all the columns in the constraint table
FROM sys.columns AS c
INNER JOIN sys.foreign_key_columns AS fkc
ON fkc.parent_column_id = c.column_id
AND fkc.parent_object_id = c.[object_id]
WHERE fkc.constraint_object_id = fk.[object_id]
ORDER BY fkc.constraint_column_id
FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'')
+ ') REFERENCES ' + QUOTENAME(rs.name) + '.' + QUOTENAME(rt.name)
+ '(' + STUFF((SELECT ',' + QUOTENAME(c.name)
-- get all the referenced columns
FROM sys.columns AS c
INNER JOIN sys.foreign_key_columns AS fkc
ON fkc.referenced_column_id = c.column_id
AND fkc.referenced_object_id = c.[object_id]
WHERE fkc.constraint_object_id = fk.[object_id]
ORDER BY fkc.constraint_column_id
FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'') + ');'
FROM sys.foreign_keys AS fk
INNER JOIN sys.tables AS rt -- referenced table
ON fk.referenced_object_id = rt.[object_id]
INNER JOIN sys.schemas AS rs
ON rt.[schema_id] = rs.[schema_id]
INNER JOIN sys.tables AS ct -- constraint table
ON fk.parent_object_id = ct.[object_id]
INNER JOIN sys.schemas AS cs
ON ct.[schema_id] = cs.[schema_id]
WHERE rt.is_ms_shipped = 0 AND ct.is_ms_shipped = 0;
SELECT @create
Запустите сгенерированный сценарий, чтобы удалить все внешние ключи, усечь таблицы, а затем запустите сгенерированный сценарий, чтобы воссоздать все внешние ключи.
Запросы берутся отсюда.
Следующее работает для меня даже с ограничениями FK и объединяет следующие ответы только для удаления указанных таблиц:
- Автоматический откат транзакции
- Цикл по списку через запятую
- Выполнение динамического SQL (с именами таблиц из переменных)
- Таблица DELETE and RESEED (в этой теме)
USE [YourDB];
DECLARE @TransactionName varchar(20) = 'stopdropandroll';
BEGIN TRAN @TransactionName;
set xact_abort on; /* automatic rollback https://stackru.com/a/1749788/1037948 */
-- ===== DO WORK // =====
-- dynamic sql placeholder
DECLARE @SQL varchar(300);
-- LOOP: https://stackru.com/a/10031803/1037948
-- list of things to loop
DECLARE @delim char = ';';
DECLARE @foreach varchar(MAX) = 'Table;Names;Separated;By;Delimiter' + @delim + 'AnotherName' + @delim + 'Still Another';
DECLARE @token varchar(MAX);
WHILE len(@foreach) > 0
BEGIN
-- set current loop token
SET @token = left(@foreach, charindex(@delim, @foreach+@delim)-1)
-- ======= DO WORK // ===========
-- dynamic sql (parentheses are required): https://stackru.com/a/989111/1037948
SET @SQL = 'DELETE FROM [' + @token + ']; DBCC CHECKIDENT (''' + @token + ''',RESEED, 0);'; -- https://stackru.com/a/11784890
PRINT @SQL;
EXEC (@SQL);
-- ======= // END WORK ===========
-- continue loop, chopping off token
SET @foreach = stuff(@foreach, 1, charindex(@delim, @foreach+@delim), '')
END
-- ===== // END WORK =====
-- review and commit
SELECT @@TRANCOUNT as TransactionsPerformed, @@ROWCOUNT as LastRowsChanged;
COMMIT TRAN @TransactionName;
Замечания:
Я думаю, что все еще помогает объявить таблицы в том порядке, в котором вы хотите их удалить (т.е. сначала убить зависимости). Как видно из этого ответа, вместо циклических имен вы можете заменить все таблицы
EXEC sp_MSForEachTable 'DELETE FROM ?; DBCC CHECKIDENT (''?'',RESEED, 0);';
Это мое решение этой проблемы. Я использовал его для изменения ПК, но идея та же. Надеюсь это будет полезно)
PRINT 'Script starts'
DECLARE @foreign_key_name varchar(255)
DECLARE @keycnt int
DECLARE @foreign_table varchar(255)
DECLARE @foreign_column_1 varchar(255)
DECLARE @foreign_column_2 varchar(255)
DECLARE @primary_table varchar(255)
DECLARE @primary_column_1 varchar(255)
DECLARE @primary_column_2 varchar(255)
DECLARE @TablN varchar(255)
-->> Type the primary table name
SET @TablN = ''
--------------------------------------------------------------------------------------- ------------------------------
--Here will be created the temporary table with all reference FKs
---------------------------------------------------------------------------------------------------------------------
PRINT 'Creating the temporary table'
select cast(f.name as varchar(255)) as foreign_key_name
, r.keycnt
, cast(c.name as varchar(255)) as foreign_table
, cast(fc.name as varchar(255)) as foreign_column_1
, cast(fc2.name as varchar(255)) as foreign_column_2
, cast(p.name as varchar(255)) as primary_table
, cast(rc.name as varchar(255)) as primary_column_1
, cast(rc2.name as varchar(255)) as primary_column_2
into #ConTab
from sysobjects f
inner join sysobjects c on f.parent_obj = c.id
inner join sysreferences r on f.id = r.constid
inner join sysobjects p on r.rkeyid = p.id
inner join syscolumns rc on r.rkeyid = rc.id and r.rkey1 = rc.colid
inner join syscolumns fc on r.fkeyid = fc.id and r.fkey1 = fc.colid
left join syscolumns rc2 on r.rkeyid = rc2.id and r.rkey2 = rc.colid
left join syscolumns fc2 on r.fkeyid = fc2.id and r.fkey2 = fc.colid
where f.type = 'F' and p.name = @TablN
ORDER BY cast(p.name as varchar(255))
---------------------------------------------------------------------------------------------------------------------
--Cursor, below, will drop all reference FKs
---------------------------------------------------------------------------------------------------------------------
DECLARE @CURSOR CURSOR
/*Fill in cursor*/
PRINT 'Cursor 1 starting. All refernce FK will be droped'
SET @CURSOR = CURSOR SCROLL
FOR
select foreign_key_name
, keycnt
, foreign_table
, foreign_column_1
, foreign_column_2
, primary_table
, primary_column_1
, primary_column_2
from #ConTab
OPEN @CURSOR
FETCH NEXT FROM @CURSOR INTO @foreign_key_name, @keycnt, @foreign_table, @foreign_column_1, @foreign_column_2,
@primary_table, @primary_column_1, @primary_column_2
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC ('ALTER TABLE ['+@foreign_table+'] DROP CONSTRAINT ['+@foreign_key_name+']')
FETCH NEXT FROM @CURSOR INTO @foreign_key_name, @keycnt, @foreign_table, @foreign_column_1, @foreign_column_2,
@primary_table, @primary_column_1, @primary_column_2
END
CLOSE @CURSOR
PRINT 'Cursor 1 finished work'
---------------------------------------------------------------------------------------------------------------------
--Here you should provide the chainging script for the primary table
---------------------------------------------------------------------------------------------------------------------
PRINT 'Altering primary table begin'
TRUNCATE TABLE table_name
PRINT 'Altering finished'
---------------------------------------------------------------------------------------------------------------------
--Cursor, below, will add again all reference FKs
--------------------------------------------------------------------------------------------------------------------
PRINT 'Cursor 2 starting. All refernce FK will added'
SET @CURSOR = CURSOR SCROLL
FOR
select foreign_key_name
, keycnt
, foreign_table
, foreign_column_1
, foreign_column_2
, primary_table
, primary_column_1
, primary_column_2
from #ConTab
OPEN @CURSOR
FETCH NEXT FROM @CURSOR INTO @foreign_key_name, @keycnt, @foreign_table, @foreign_column_1, @foreign_column_2,
@primary_table, @primary_column_1, @primary_column_2
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC ('ALTER TABLE [' +@foreign_table+ '] WITH NOCHECK ADD CONSTRAINT [' +@foreign_key_name+ '] FOREIGN KEY(['+@foreign_column_1+'])
REFERENCES [' +@primary_table+'] (['+@primary_column_1+'])')
EXEC ('ALTER TABLE [' +@foreign_table+ '] CHECK CONSTRAINT [' +@foreign_key_name+']')
FETCH NEXT FROM @CURSOR INTO @foreign_key_name, @keycnt, @foreign_table, @foreign_column_1, @foreign_column_2,
@primary_table, @primary_column_1, @primary_column_2
END
CLOSE @CURSOR
PRINT 'Cursor 2 finished work'
---------------------------------------------------------------------------------------------------------------------
PRINT 'Temporary table droping'
drop table #ConTab
PRINT 'Finish'
Если ни один из этих ответов не сработал так, как в моем случае, сделайте следующее:
- Отбросьте ограничения
- Установите все значения, чтобы разрешить нули
- Усеченный стол
- Добавьте ограничения, которые были удалены.
Удачи!
Удалить, а затем сбросить автоинкремент:
delete from tablename;
затем
ALTER TABLE tablename AUTO_INCREMENT = 1;
Следующий скрипт усекает все ограничения внешнего ключа и воссоздает их:
DECLARE @DROP_CONSTRAINT_SCRIPT VARCHAR(MAX);
DECLARE @CREATE_CONSTRAINT_SCRIPT VARCHAR(MAX);
SET @DROP_CONSTRAINT_SCRIPT='';
SET @CREATE_CONSTRAINT_SCRIPT='';
SELECT @DROP_CONSTRAINT_SCRIPT=@DROP_CONSTRAINT_SCRIPT + 'ALTER TABLE '+FK_Table+'
DROP CONSTRAINT '+FK_Name+';
',
@CREATE_CONSTRAINT_SCRIPT = @CREATE_CONSTRAINT_SCRIPT +
'ALTER TABLE '+FK_Table+'
ADD CONSTRAINT '+FK_Name+'
FOREIGN KEY ('+FK_Column+') REFERENCES '+PK_Table+'('+PK_Column+');
'
FROM (
SELECT RC.CONSTRAINT_NAME FK_Name
, KF.TABLE_SCHEMA FK_Schema
, KF.TABLE_NAME FK_Table
, KF.COLUMN_NAME FK_Column
, RC.UNIQUE_CONSTRAINT_NAME PK_Name
, KP.TABLE_SCHEMA PK_Schema
, KP.TABLE_NAME PK_Table
, KP.COLUMN_NAME PK_Column
, RC.MATCH_OPTION MatchOption
, RC.UPDATE_RULE UpdateRule
, RC.DELETE_RULE DeleteRule
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KF ON RC.CONSTRAINT_NAME = KF.CONSTRAINT_NAME
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KP ON RC.UNIQUE_CONSTRAINT_NAME = KP.CONSTRAINT_NAME
WHERE KP.TABLE_NAME='TABLE_NAME_TRUNCATE'
) TBL
-- DROP CONSTRAINTS
EXEC( @DROP_CONSTRAINT_SCRIPT);
-------
-- TRUNCATE TABLE SCRIPT NEEDS TO BE PUT BELOW
-------
TRUNCATE TABLE TABLE_NAME_TRUNCATE
-- RECREATE CONSTRAINTS
EXEC( @CREATE_CONSTRAINT_SCRIPT);
Увидев, насколько хорошо работает это решение , я собрал удобную хранимую процедуру для простоты использования.
CREATE OR ALTER PROCEDURE ResetTable
(
@database NVARCHAR(128),
@schema NVARCHAR(128),
@table NVARCHAR(128)
)
AS
BEGIN
BEGIN TRANSACTION;
DECLARE @sql NVARCHAR(MAX);
SET @sql = 'DELETE FROM ' + QUOTENAME(@schema) + '.' + QUOTENAME(@table) + ';' +
'DBCC CHECKIDENT (''' + QUOTENAME(@database) + '.' + QUOTENAME(@schema) + '.' + QUOTENAME(@table) + ''', RESEED, 0);';
BEGIN TRY
EXEC sp_executesql @sql;
COMMIT;
END TRY
BEGIN CATCH
ROLLBACK;
END CATCH;
END;
Пример использования:
EXEC ResetTable @database='MyDatabase', @schema='dbo', @table='Users';
Если вы делаете это с любой частотой, чёрт возьми, даже по расписанию, я бы абсолютно и безоговорочно никогда не использовал оператор DML. Стоимость записи в журнал транзакций слишком высока, и установка всей базы данных в SIMPLE Режим восстановления для усечения одной таблицы смешон.
Лучший способ, к сожалению, трудный или трудоемкий. Это существо:
- Отбросьте ограничения
- Усеченный стол
- Восстановить ограничения
Мой процесс для этого включает в себя следующие шаги:
- В SSMS щелкните правой кнопкой мыши по рассматриваемой таблице и выберите View Dependencies
- Обратите внимание на таблицы, на которые есть ссылки (если есть)
- Вернитесь в проводник объектов, разверните узел Ключи и запишите внешние ключи (если есть)
- Запустить сценарии (удалить / усечь / заново создать)
Сценарии такого рода должны быть сделаны в течение begin tran а также commit tran блок.
В SSMS у меня была открытая диаграмма, показывающая ключ. После удаления ключа и усечения файла я обновил его, затем снова сосредоточился на диаграмме и создал обновление, очистив и восстановив поле Identity. При сохранении диаграммы появилось диалоговое окно "Сохранить", после чего в диалоговом окне "Изменения были внесены в базу данных во время работы", нажав "Да", восстановил ключ и восстановил его из заблокированной копии на диаграмме.
Черт возьми - так много ответов... и я рискну вставить другой. Мне нужно было что-то, чтобы периодически удалять подмножество таблиц в производственных базах данных, и почти все существующие ответы имели какую-то проблему, которая не отвечала моим интересам... которые были:
- Мне нужно было иметь дело с ограничениями, которые имеют такие имена, как
ThisTable.to.ThatTable - Список таблиц может охватывать несколько схем
- Список усеченных таблиц может варьироваться - поэтому мне нужен ввод
- Есть внешние ключи с
on delete cascadeи другие действия - Если после усечения я не могу восстановить внешние ключи, мне нужно выполнить откат
- Мне нужно ограничить область удаления / повторного добавления внешних ключей только для затронутых таблиц
- Мне нужны как минимум минимальные проверки работоспособности, так как мы заставляем клиентов запускать этот беспорядок
- Я хочу, чтобы клиенты запускали что, если перед тем, как делать это по-настоящему
- Я не могу себе позволить
delete tableпо ряду причин
Я также хотел что-то более понятное и понятное, чем некоторые сценарии, которые я изучал. Я не знаю... Я думаю, это субъективно. Вы можете ненавидеть мой стиль кодирования.
Во всяком случае, он предварительно обрабатывает, выбирая в какой-то большой nvarchar(max) переменные: одна для удаления внешних ключей, одна для отключения триггеров / усечения таблицы / включения триггеров и, наконец, одна для восстановления внешних ключей. Затем он либо печатает, либо выполняет результаты.
Ожидается, что в качестве входного аргумента будет задан пользовательский тип таблицы. У нас есть такая вещь во всех наших базах данных. Этот тип определяется так:
create type dbo.NamesType as table( Name sysname )
Чтобы вызвать его для некоторых одноразовых таблиц в нескольких схемах со странными именами ограничений (и, возможно, с небольшим злонамеренным вводом), вот суть моего тестового набора:
create schema nop authorization dbo
go
create schema gak authorization dbo
go
create table nop.a(one int primary key)
create table gak.b(one int constraint [b.to.a] foreign key references nop.a)
go
--> do the damage...
declare @t dbo.NamesType
insert @t values ('nop.a'),('gak.b') --,(';drop database'),('no.bobby.tables')
execute dbo.TruncateTables @t --, 0
go
drop table gak.b
drop table nop.a
go
drop schema gak
go
drop schema nop
... который печатает:
alter table gak.b drop constraint [b.to.a];
alter table nop.a disable trigger all;
truncate table nop.a;
alter table nop.a enable trigger all;
alter table gak.b disable trigger all;
truncate table gak.b;
alter table gak.b enable trigger all;
alter table gak.b add constraint [b.to.a] foreign key ( one ) references nop.a( one );
И, наконец, вот клубок пряжи. Надеюсь, это кому- нибудь поможет;-)
create procedure dbo.TruncateTables
@tableNames dbo.NamesType readonly, --> Note: create type dbo.NamesType as table( Name sysname )
@printOnly bit = 1 --> by default, it only prints...you have to explicly set to 0 to get it to do the damage
as
begin
set nocount on
declare
@dropFKsSql nvarchar( max ) = N'',
@addFKsSql nvarchar( max ) = N'',
@truncateSql nvarchar( max ) = N'',
@cr nvarchar( 2 ) = nchar( 13 ) + nchar( 10 ),
@numberOfInputs int,
@numberOfEligible int
declare
@truncatableTables table( object_id int not null )
insert @truncatableTables
select t.object_id
from
@tableNames n
inner join
sys.tables t
on
object_id( n.Name ) = t.object_id
select @numberOfInputs = count(*) from @tableNames
select @numberOfEligible = count(*) from @truncatableTables
if ( @numberOfEligible < @numberOfInputs )
begin
raiserror( 'At least one of the inputs is not a table in this database', 16, 1 )
return 1
end
select
@truncateSql +=
N'alter table ' + schema_name(t.schema_id) + N'.' + t.name + N' disable trigger all;' + @cr +
N'truncate table ' + schema_name(t.schema_id) + N'.' + t.name + N';' + @cr +
N'alter table ' + schema_name(t.schema_id) + N'.' + t.name + N' enable trigger all;' + @cr
from
sys.tables t
where
t.object_id in ( select object_id from @truncatableTables )
select
@dropFKsSql +=
N'alter table '+ schema_name(from_table.schema_id) + N'.' + from_table.name +
N' drop constraint [' + fk.name + N']'+
N';' + @cr,
@addFKsSql +=
N'alter table '+ schema_name(from_table.schema_id) + N'.' + from_table.name +
N' add constraint [' + fk.name + N'] foreign key ( ' + fk_cols.from_columns + N' ) references ' +
schema_name(to_table.schema_id) + N'.' + to_table.name + N'( ' + fk_cols.to_columns + N' )' +
case (fk.delete_referential_action)
when 1 then N' on delete cascade'
when 2 then N' on delete set null'
when 3 then N' on delete set default'
else N''
end +
case (fk.update_referential_action)
when 1 then N' on update cascade'
when 2 then N' on update set null'
when 3 then N' on update set default'
else N''
end +
N';' + @cr
from
sys.foreign_keys fk
inner join
sys.objects from_table
on
fk.parent_object_id = from_table.object_id
inner join
sys.objects to_table
on
fk.referenced_object_id = to_table.object_id
inner join
(
select
o.constraint_object_id,
stuff( (
select N',' + col_name(fkc.parent_object_id, fkc.parent_column_id)
from sys.foreign_key_columns fkc
where fkc.constraint_object_id = o.constraint_object_id
order by fkc.constraint_column_id --> has to be same order in to_columns (below)
for xml path( '' )
), 1, 1, N'' ) as from_columns,
stuff( (
select N',' + col_name(fkc.referenced_object_id, fkc.referenced_column_id)
from sys.foreign_key_columns fkc
where fkc.constraint_object_id = o.constraint_object_id
order by fkc.constraint_column_id --> has to be in same order as from_colums (above)
for xml path( '' )
), 1, 1, N'' ) as to_columns
from
sys.foreign_key_columns o
where
o.constraint_column_id = 1 --> just need a representative row
group by
constraint_object_id
) as fk_cols
on
fk.object_id = fk_cols.constraint_object_id
where
fk.referenced_object_id in ( select object_id from @truncatableTables ) --> limits scope
print @dropFKsSql
print @truncateSql
print @addFKsSql
if ( @printOnly = 0 )
begin
begin transaction --> won't have this luxury in azure
execute sp_executesql @statement = @dropFKsSql
execute sp_executesql @statement = @truncateSql
execute sp_executesql @statement = @addFKsSql
print 'Got this far, and it didn''t blow up!'
commit transaction
end
end
В Azure вы не можете запустить truncate table в сделке (грррр....). В такой среде мне нужно заранее обеспечить наличие записей во внешних ключах, которые не участвуют в усечении. Не безнадежно сложный, но пока не необходимый для меня.
Это пример для тех, кто использует Entity Framework.
Таблица для сброса:
Еще одна таблица, которая зависит от:
Столбец ограничений в таблице
Foo:FooColumnСтолбец ограничений в таблице
Bar:BarColumnpublic override void Down() { DropForeignKey("dbo.Bar", "BarColumn", "dbo.Foo"); Sql("TRUNCATE TABLE Foo"); AddForeignKey("dbo.Bar", "BarColumn", "dbo.Foo", "FooColumn", cascadeDelete: true); }
Я только что обнаружил, что вы можете использовать таблицу TRUNCATE для родительской таблицы с ограничениями внешнего ключа для дочернего элемента, если сначала вы отключите ограничения для дочерней таблицы. Например
Внешний ключ CONSTRAINT child_par_ref для дочерней таблицы, ссылается на PARENT_TABLE
ALTER TABLE CHILD_TABLE DISABLE CONSTRAINT child_par_ref;
TRUNCATE TABLE CHILD_TABLE;
TRUNCATE TABLE PARENT_TABLE;
ALTER TABLE CHILD_TABLE ENABLE CONSTRAINT child_par_ref;
Вы могли бы попробовать DELETE FROM <your table >;,
Сервер покажет вам имя ограничения и таблицу, и, удалив эту таблицу, вы можете удалить то, что вам нужно.