Как удалить все строки из всех таблиц в базе данных SQL Server?

Обратите внимание, что TRUNCATE не будет работать, если у вас есть какая-либо ссылочная целостность.

В этом случае это будет работать:

EXEC sp_MSForEachTable 'DISABLE TRIGGER ALL ON ?' GO EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL' GO EXEC sp_MSForEachTable 'DELETE FROM ?' GO EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL' GO EXEC sp_MSForEachTable 'ENABLE TRIGGER ALL ON ?' GO 

В моем недавнем проекте моя задача состояла в том, чтобы очистить всю базу данных с помощью оператора sql и каждой таблицы, имеющей множество ограничений, таких как первичный ключ и внешний ключ. В базе данных содержится более 1000 таблиц, поэтому невозможно написать запрос на удаление для каждой таблицы.

Используя хранимую процедуру sp_MSForEachTable, которая позволяет нам легко обрабатывать некоторый код для каждой таблицы в одной базе данных. Это означает, что он используется для обработки одной команды T-SQL или различных команд T-SQL для каждой таблицы в базе данных.

Поэтому выполните следующие шаги, чтобы обрезать все таблицы в базе данных SQL Server:

Шаг 1. Отключите все ограничения в базе данных, используя следующий sql-запрос:

EXEC sys.sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'

Шаг 2. Выполните операцию Delete или truncate в каждой таблице базы данных с помощью команды sql:

 EXEC sys.sp_msforeachtable 'DELETE FROM ?' 

Шаг 3. Включите все ограничения в базе данных, используя следующую команду sql:

 EXEC sys.sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL' 

Мне пришлось удалить все строки и сделать это со следующим скриптом:

 DECLARE @Nombre NVARCHAR(MAX); DECLARE curso CURSOR FAST_FORWARD FOR Select Object_name(object_id) AS Nombre from sys.objects where type = 'U' OPEN curso FETCH NEXT FROM curso INTO @Nombre WHILE (@@FETCH_STATUS <> -1) BEGIN IF (@@FETCH_STATUS <> -2) BEGIN DECLARE @statement NVARCHAR(200); SET @statement = 'DELETE FROM ' + @Nombre; print @statement execute sp_executesql @statement; END FETCH NEXT FROM curso INTO @Nombre END CLOSE curso DEALLOCATE curso 

Надеюсь это поможет!

Вот решение, которое:

1. Ограничения капли (спасибо этому сообщению)
2. Итерации через INFORMATION_SCHEMA.TABLES для конкретной базы данных
3. Таблицы SELECTS основанные на некоторых критериях поиска
4. Удаляет все данные из этих таблиц
5. Повторно добавляет ограничения
6. Позволяет игнорировать определенные таблицы, такие как sysdiagrams и __RefactorLog

Сначала я попробовал EXECUTE sp_MSforeachtable 'TRUNCATE TABLE ?' , но это удалило мои диаграммы.

 USE ; GO -- Disable all constraints in the database EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all" declare @catalog nvarchar(250); declare @schema nvarchar(250); declare @tbl nvarchar(250); DECLARE i CURSOR LOCAL FAST_FORWARD FOR select TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME != 'sysdiagrams' AND TABLE_NAME != '__RefactorLog' OPEN i; FETCH NEXT FROM i INTO @catalog, @schema, @tbl; WHILE @@FETCH_STATUS = 0 BEGIN DECLARE @sql NVARCHAR(MAX) = N'DELETE FROM [' + @catalog + '].[' + @schema + '].[' + @tbl + '];' /* Make sure these are the commands you want to execute before executing */ PRINT 'Executing statement: ' + @sql -- EXECUTE sp_executesql @sql FETCH NEXT FROM i INTO @catalog, @schema, @tbl; END CLOSE i; DEALLOCATE i; -- Re-enable all constraints again EXEC sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all" 

 Set nocount on Exec sp_MSForEachTable 'Alter Table ? NoCheck Constraint All' Exec sp_MSForEachTable ' If ObjectProperty(Object_ID(''?''), ''TableHasForeignRef'')=1 Begin -- Just to know what all table used delete syntax. Print ''Delete from '' + ''?'' Delete From ? End Else Begin -- Just to know what all table used Truncate syntax. Print ''Truncate Table '' + ''?'' Truncate Table ? End ' Exec sp_MSForEachTable 'Alter Table ? Check Constraint All' 

Вы можете удалить все строки из всех таблиц с помощью подхода, предложенного Рубеном, или просто отбросить и воссоздать все таблицы. Всегда хорошая идея иметь сценарии создания полного db, так что это может быть самый простой / быстрый метод.

В моем случае мне нужно было установить QUOTED_IDENTIFIER. Это привело к небольшой модификации ответа Марка Рендла выше:

 EXEC sp_MSForEachTable 'DISABLE TRIGGER ALL ON ?' GO EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL' GO EXEC sp_MSForEachTable 'SET QUOTED_IDENTIFIER ON; DELETE FROM ?' GO EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL' GO EXEC sp_MSForEachTable 'ENABLE TRIGGER ALL ON ?' GO 

Для некоторых требований нам, возможно, придется пропустить определенные таблицы. Я написал сценарий ниже, чтобы добавить дополнительные условия для фильтрации списка таблиц. В приведенном ниже скрипте также будет отображаться счетчик предварительного удаления и пост-удаления.

  IF OBJECT_ID('TEMPDB..#TEMPRECORDCOUNT') IS NOT NULL DROP TABLE #TEMPRECORDCOUNT CREATE TABLE #TEMPRECORDCOUNT ( TABLENAME NVARCHAR(128) ,PREDELETECOUNT BIGINT ,POSTDELETECOUNT BIGINT ) INSERT INTO #TEMPRECORDCOUNT (TABLENAME, PREDELETECOUNT, POSTDELETECOUNT) SELECT O.name TableName ,DDPS.ROW_COUNT PREDELETECOUNT ,NULL FROM sys.objects O INNER JOIN ( SELECT OBJECT_ID, SUM(row_count) ROW_COUNT FROM SYS.DM_DB_PARTITION_STATS GROUP BY OBJECT_ID ) DDPS ON DDPS.OBJECT_ID = O.OBJECT_ID WHERE O.type = 'U' AND O.name NOT LIKE 'OC%' AND O.schema_id = 1 DECLARE @TableName NVARCHAR(MAX); DECLARE TableDeleteCursor CURSOR FAST_FORWARD FOR SELECT TableName from #TEMPRECORDCOUNT OPEN TableDeleteCursor FETCH NEXT FROM TableDeleteCursor INTO @TableName WHILE (@@FETCH_STATUS <> -1) BEGIN IF (@@FETCH_STATUS <> -2) BEGIN DECLARE @STATEMENT NVARCHAR(MAX); SET @STATEMENT = ' DISABLE TRIGGER ALL ON ' + @TableName + '; ALTER TABLE ' + @TableName + ' NOCHECK CONSTRAINT ALL' + '; DELETE FROM ' + @TableName + '; ALTER TABLE ' + @TableName + ' CHECK CONSTRAINT ALL' + '; ENABLE TRIGGER ALL ON ' + @TableName; PRINT @STATEMENT EXECUTE SP_EXECUTESQL @STATEMENT; END FETCH NEXT FROM TableDeleteCursor INTO @TableName END CLOSE TableDeleteCursor DEALLOCATE TableDeleteCursor UPDATE T SET T.POSTDELETECOUNT = I.ROW_COUNT FROM #TEMPRECORDCOUNT T INNER JOIN ( SELECT O.name TableName, DDPS.ROW_COUNT ROW_COUNT FROM sys.objects O INNER JOIN ( SELECT OBJECT_ID, SUM(row_count) ROW_COUNT FROM SYS.DM_DB_PARTITION_STATS GROUP BY OBJECT_ID ) DDPS ON DDPS.OBJECT_ID = O.OBJECT_ID WHERE O.type = 'U' AND O.name NOT LIKE 'OC%' AND O.schema_id = 1 ) I ON I.TableName COLLATE DATABASE_DEFAULT = T.TABLENAME SELECT * FROM #TEMPRECORDCOUNT ORDER BY TABLENAME ASC 

если вы хотите удалить всю таблицу, вы должны следовать следующей инструкции SQL

 Delete FROM TABLE Where PRIMARY_KEY_ is Not NULL;