SQL-TRUNCATE-DATENBANK? Alle Tabellen abschneiden
Ich benutze SQL Server 2008 R2.
Gibt es einen SQL-Befehl zum Leeren der Datenbank, anstatt alle 20 meiner Tabellen abschneiden zu müssen?
Ich möchte nur die Daten löschen, nicht die Struktur.
Sie können die gespeicherte Prozedur sp_MSforeachtable folgendermaßen verwenden:
USE MyDatabase
EXEC sp_MSforeachtable 'TRUNCATE TABLE ?'
Seien Sie gewarnt, dass dadurch ALLE Daten aus allen Benutzertabellen gelöscht werden (durch Abschneiden). Und falls Sie aufgrund von Fremdschlüsseln usw. kein TRUNCATE durchführen können, können Sie dasselbe wie ein Löschen ausführen:
USE MyDatabase
EXEC sp_MSforeachtable 'DELETE FROM ?'
Ich benutze dieses Skript
EXEC sp_MSForEachTable ‘ALTER TABLE ? NOCHECK CONSTRAINT ALL’
EXEC sp_MSForEachTable ‘DELETE FROM ?’
EXEC sp_MSForEachTable ‘ALTER TABLE ? CHECK CONSTRAINT ALL’
GO
Die akzeptierte Antwort funktioniert nicht ganz, wenn Sie Fremdschlüsselbeziehungen haben. In diesem Fall müssen Sie die Einschränkungen löschen und neu erstellen. Nachfolgend finden Sie eine gespeicherte Prozedur, die auf der Antwort basiert. hier
CREATE PROCEDURE [dbo].[deleteAllDataFromAllTables] AS
SET NOCOUNT ON
DECLARE @dropAndCreateConstraintsTable TABLE ( DropStmt varchar(max) , CreateStmt varchar(max) )
insert @dropAndCreateConstraintsTable select
DropStmt = 'ALTER TABLE [' + ForeignKeys.ForeignTableSchema +
'].[' + ForeignKeys.ForeignTableName +
'] DROP CONSTRAINT [' + ForeignKeys.ForeignKeyName + ']; '
, CreateStmt = 'ALTER TABLE [' + ForeignKeys.ForeignTableSchema +
'].[' + ForeignKeys.ForeignTableName +
'] WITH CHECK ADD CONSTRAINT [' + ForeignKeys.ForeignKeyName +
'] FOREIGN KEY([' + ForeignKeys.ForeignTableColumn +
']) REFERENCES [' + schema_name(sys.objects.schema_id) + '].[' +
sys.objects.[name] + ']([' +
sys.columns.[name] + ']); '
from sys.objects
inner join sys.columns
on (sys.columns.[object_id] = sys.objects.[object_id])
inner join (
select sys.foreign_keys.[name] as ForeignKeyName
,schema_name(sys.objects.schema_id) as ForeignTableSchema
,sys.objects.[name] as ForeignTableName
,sys.columns.[name] as ForeignTableColumn
,sys.foreign_keys.referenced_object_id as referenced_object_id
,sys.foreign_key_columns.referenced_column_id as referenced_column_id
from sys.foreign_keys
inner join sys.foreign_key_columns
on (sys.foreign_key_columns.constraint_object_id
= sys.foreign_keys.[object_id])
inner join sys.objects
on (sys.objects.[object_id]
= sys.foreign_keys.parent_object_id)
inner join sys.columns
on (sys.columns.[object_id]
= sys.objects.[object_id])
and (sys.columns.column_id
= sys.foreign_key_columns.parent_column_id)
) ForeignKeys
on (ForeignKeys.referenced_object_id = sys.objects.[object_id])
and (ForeignKeys.referenced_column_id = sys.columns.column_id)
where (sys.objects.[type] = 'U')
and (sys.objects.[name] not in ('sysdiagrams'))
DECLARE @DropStatement nvarchar(max)
DECLARE @RecreateStatement nvarchar(max)
DECLARE C1 CURSOR READ_ONLY
FOR
SELECT DropStmt from @dropAndCreateConstraintsTable
OPEN C1
FETCH NEXT FROM C1 INTO @DropStatement
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Executing ' + @DropStatement
execute sp_executesql @DropStatement
FETCH NEXT FROM C1 INTO @DropStatement
END
CLOSE C1
DEALLOCATE C1
DECLARE @DeleteTableStatement nvarchar(max)
DECLARE C2 CURSOR READ_ONLY
FOR
SELECT 'Delete From [dbo].[' + TABLE_NAME + ']' from INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'dbo' -- Change your schema appropriately.
OPEN C2
FETCH NEXT FROM C2 INTO @DeleteTableStatement
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Executing ' + @DeleteTableStatement
execute sp_executesql @DeleteTableStatement
FETCH NEXT FROM C2 INTO @DeleteTableStatement
END
CLOSE C2
DEALLOCATE C2
DECLARE C3 CURSOR READ_ONLY
FOR
SELECT CreateStmt from @dropAndCreateConstraintsTable
OPEN C3
FETCH NEXT FROM C3 INTO @RecreateStatement
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Executing ' + @RecreateStatement
execute sp_executesql @RecreateStatement
FETCH NEXT FROM C3 INTO @RecreateStatement
END
CLOSE C3
DEALLOCATE C3
GO
führe das aus
EXEC sp_MSforeachtable 'PRINT ''ALTER TABLE ? NOCHECK CONSTRAINT ALL'''
EXEC sp_MSforeachtable 'print ''DELETE FROM ?'''
EXEC sp_MSforeachtable 'print ''ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all'''
Nachdem Sie das gedruckte Ergebnis kopiert haben, fügen Sie es in das Abfragefeld ein und führen Sie es aus. Es werden alle Tabellen abgeschnitten.
Die meisten der angebotenen Lösungen verwenden entweder TRUNCATE nicht, was sich von DELETE unterscheidet, oder sie behandeln nicht das Problem der Fremdschlüsseleinschränkungen. Die von Chaitanya bereitgestellte Lösung kommt nahe heran, greift jedoch auf DELETE zurück, und zwar in einer gespeicherten Prozedur, die scheinbar nicht in eine Situation passt, in der Sie alle Daten in einer Datenbank abfragen.
Im Folgenden finden Sie meine Variation, die TRUNCATE verwendet und das Fremdschlüsseleinschränkungsproblem anspricht.
/* TRUNCATE ALL TABLES IN A DATABASE */
DECLARE @dropAndCreateConstraintsTable TABLE
(
DropStmt VARCHAR(MAX)
,CreateStmt VARCHAR(MAX)
)
/* Gather information to drop and then recreate the current foreign key constraints */
INSERT @dropAndCreateConstraintsTable
SELECT DropStmt = 'ALTER TABLE [' + ForeignKeys.ForeignTableSchema
+ '].[' + ForeignKeys.ForeignTableName + '] DROP CONSTRAINT ['
+ ForeignKeys.ForeignKeyName + ']; '
,CreateStmt = 'ALTER TABLE [' + ForeignKeys.ForeignTableSchema
+ '].[' + ForeignKeys.ForeignTableName
+ '] WITH CHECK ADD CONSTRAINT [' + ForeignKeys.ForeignKeyName
+ '] FOREIGN KEY([' + ForeignKeys.ForeignTableColumn
+ ']) REFERENCES [' + SCHEMA_NAME(sys.objects.schema_id)
+ '].[' + sys.objects.[name] + ']([' + sys.columns.[name]
+ ']); '
FROM sys.objects
INNER JOIN sys.columns
ON ( sys.columns.[object_id] = sys.objects.[object_id] )
INNER JOIN ( SELECT sys.foreign_keys.[name] AS ForeignKeyName
,SCHEMA_NAME(sys.objects.schema_id) AS ForeignTableSchema
,sys.objects.[name] AS ForeignTableName
,sys.columns.[name] AS ForeignTableColumn
,sys.foreign_keys.referenced_object_id AS referenced_object_id
,sys.foreign_key_columns.referenced_column_id AS referenced_column_id
FROM sys.foreign_keys
INNER JOIN sys.foreign_key_columns
ON ( sys.foreign_key_columns.constraint_object_id = sys.foreign_keys.[object_id] )
INNER JOIN sys.objects
ON ( sys.objects.[object_id] = sys.foreign_keys.parent_object_id )
INNER JOIN sys.columns
ON ( sys.columns.[object_id] = sys.objects.[object_id] )
AND ( sys.columns.column_id = sys.foreign_key_columns.parent_column_id )
) ForeignKeys
ON ( ForeignKeys.referenced_object_id = sys.objects.[object_id] )
AND ( ForeignKeys.referenced_column_id = sys.columns.column_id )
WHERE ( sys.objects.[type] = 'U' )
AND ( sys.objects.[name] NOT IN ( 'sysdiagrams' ) )
/* SELECT * FROM @dropAndCreateConstraintsTable AS DACCT */
DECLARE @DropStatement NVARCHAR(MAX)
DECLARE @RecreateStatement NVARCHAR(MAX)
/* Drop Constraints */
DECLARE C1 CURSOR READ_ONLY
FOR
SELECT DropStmt
FROM @dropAndCreateConstraintsTable
OPEN C1
FETCH NEXT FROM C1 INTO @DropStatement
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Executing ' + @DropStatement
EXECUTE sp_executesql @DropStatement
FETCH NEXT FROM C1 INTO @DropStatement
END
CLOSE C1
DEALLOCATE C1
/* Truncate all tables in the database in the dbo schema */
DECLARE @DeleteTableStatement NVARCHAR(MAX)
DECLARE C2 CURSOR READ_ONLY
FOR
SELECT 'TRUNCATE TABLE [dbo].[' + TABLE_NAME + ']'
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'dbo'
AND TABLE_TYPE = 'BASE TABLE'
/* Change your schema appropriately if you don't want to use dbo */
OPEN C2
FETCH NEXT FROM C2 INTO @DeleteTableStatement
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Executing ' + @DeleteTableStatement
EXECUTE sp_executesql @DeleteTableStatement
FETCH NEXT FROM C2 INTO @DeleteTableStatement
END
CLOSE C2
DEALLOCATE C2
/* Recreate foreign key constraints */
DECLARE C3 CURSOR READ_ONLY
FOR
SELECT CreateStmt
FROM @dropAndCreateConstraintsTable
OPEN C3
FETCH NEXT FROM C3 INTO @RecreateStatement
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Executing ' + @RecreateStatement
EXECUTE sp_executesql @RecreateStatement
FETCH NEXT FROM C3 INTO @RecreateStatement
END
CLOSE C3
DEALLOCATE C3
GO
DECLARE @nombre NVARCHAR(100)
DECLARE @tablas TABLE(nombre nvarchar(100))
INSERT INTO @tablas
SELECT t.TABLE_SCHEMA+ '.'+t.TABLE_NAME FROM INFORMATION_SCHEMA.TABLES T
DECLARE @contador INT=0
SELECT @contador=COUNT(*) FROM INFORMATION_SCHEMA.TABLES
WHILE @contador>0
BEGIN
SELECT TOP 1 @nombre=nombre FROM @tablas
DECLARE @sql NVARCHAR(500)=''
SET @sql [email protected]+'Truncate table '[email protected]
EXEC (@sql)
SELECT @sql
SET @[email protected]
DELETE TOP (1) @tablas
END
Kein Unsinn-Skript dieses:
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
EXEC sp_MSForEachTable 'DELETE FROM ?'
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
EXEC sp_MSforeachtable 'DBCC CHECKIDENT ( ''?'', RESEED, 0)'
GO
Das Abschneiden funktioniert immer noch nicht, wenn Sie Fremdschlüssel in den Tabellen haben. Dieses Skript setzt auch alle Identitätsspalten zurück.
Ich habe einen Punkt sowohl von Boycs Answer als auch von mtmurdocks nachfolgender Antwort genommen. Ich habe die folgende Prozedur in all meinen Entwicklungs- oder Staging-Datenbanken gespeichert. Ich habe einige Schalter hinzugefügt, um meine eigenen Anforderungen zu erfüllen, wenn ich Anweisungen hinzufügen muss, um die Daten für bestimmte Spalten neu zu bestimmen.
( Anmerkung: Ich hätte dies als Kommentar zu Boycs brillanter Antwort hinzugefügt, aber ich habe noch nicht genug Ruf, um das zu tun. Bitte entschuldigen Sie, dass ich dies als eine völlig neue Antwort hinzugefügt habe.)
ALTER PROCEDURE up_ResetEntireDatabase
@IncludeIdentReseed BIT,
@IncludeDataReseed BIT
AS
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
EXEC sp_MSForEachTable 'DELETE FROM ?'
IF @IncludeIdentReseed = 1
BEGIN
EXEC sp_MSForEachTable 'DBCC CHECKIDENT (''?'' , RESEED, 1)'
END
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
IF @IncludeDataReseed = 1
BEGIN
-- Populate Core Data Table Here
END
GO
Und einmal fertig ist die Ausführung ganz einfach:
EXEC up_ResetEntireDatabase 1, 1
Sie können diese gespeicherte Prozedur auch verwenden, wenn Sie nur alle Tabellen in einem bestimmten Schema abschneiden möchten:
-- =============================================
-- Author: Ben de Ridder
-- Create date: 20160513
-- Description: Truncate all tables in schema
-- =============================================
CREATE PROCEDURE TruncateAllTablesInSchema
@schema nvarchar(50)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @table nVARCHAR(255)
DECLARE db_cursor CURSOR FOR
select t.name
from sys.tables t inner join
sys.schemas s on
t.schema_id=s.schema_id
where [email protected]
order by 1
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @table
WHILE @@FETCH_STATUS = 0
BEGIN
declare @sql nvarchar(1000)
set @sql = 'truncate table [' + @schema + '].[' + @table + ']'
exec sp_sqlexec @sql
FETCH NEXT FROM db_cursor INTO @table
END
CLOSE db_cursor
DEALLOCATE db_cursor
END
GO
---- Remove Constraint ----
EXEC sp_MSForEachTable "ALTER TABLE ? NOCHECK CONSTRAINT all"
---- Delete Data ----
EXEC sp_MSForEachTable "DELETE FROM ?"
---- Add Constraint ----
EXEC sp_MSForEachTable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"
---- Reset Identity value ----
EXEC sp_MSForEachTable "DBCC CHECKIDENT ( '?', RESEED, 0)"