Wie kann man mit TSQL alle Tabellen in einer Datenbank abschneiden?

Question

Ich habe eine Testumgebung für eine Datenbank, die ich zu Beginn eines Testzyklus mit neuen Daten neu laden möchte. Ich möchte nicht die gesamte Datenbank neu aufbauen, sondern nur die Daten "neu einstellen".

Was ist der beste Weg, um alle Daten aus allen Tabellen mit TSQL zu entfernen? Gibt es gespeicherte Systemprozeduren, Ansichten usw., die verwendet werden können? Ich möchte nicht für jede Tabelle manuell eine Anweisung zum Abschneiden von Tabellen erstellen und pflegen - ich würde es vorziehen, wenn es dynamisch wäre.

Answer 1

Wenn es darum geht, Daten aus Tabellen zu löschen, die Fremdschlüsselbeziehungen haben - was im Grunde bei jeder richtig konzipierten Datenbank der Fall ist - können wir alle Beschränkungen deaktivieren, alle Daten löschen und dann die Beschränkungen wieder aktivieren

-- disable all constraints
EXEC sp_MSForEachTable "ALTER TABLE ? NOCHECK CONSTRAINT all"

-- delete data in all tables
EXEC sp_MSForEachTable "DELETE FROM ?"

-- enable all constraints
exec sp_MSForEachTable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

Mehr über die Deaktivierung von Beschränkungen und Auslösern ici

wenn einige der Tabellen Identitätsspalten haben, wollen wir sie vielleicht neu sortieren

EXEC sp_MSForEachTable "DBCC CHECKIDENT ( '?', RESEED, 0)"

Beachten Sie, dass sich das Verhalten von RESEED zwischen einer brandneuen Tabelle und einer Tabelle unterscheidet, in die zuvor Daten aus BOL :

DBCC CHECKIDENT ('tabellenname', RESEED, newReseedValue)

Der aktuelle Identitätswert wird auf den newReseedValue gesetzt. Wenn keine Zeilen Zeilen in die Tabelle eingefügt wurden, seit sie erstellt wurde, wird die erste eingefügte Zeile nach der Ausführung von DBCC CHECKIDENT newReseedValue als Identität verwenden. Andernfalls wird die nächste eingefügte Zeile newReseedValue + 1 verwendet. Wenn der Wert von newReseedValue kleiner ist als der Maximalwert in der Identitätsspalte, wird die Fehlermeldung 2627 erzeugt bei nachfolgenden Verweisen auf die Tabelle erzeugt.

Dank an Robert für den Hinweis auf die Tatsache, dass die Deaktivierung von Beschränkungen die Verwendung von truncate nicht zulässt; die Beschränkungen müssten gelöscht und dann neu erstellt werden

Answer 2

Für SQL 2005,

EXEC sp_MSForEachTable 'TRUNCATE TABLE ?'

Ein paar weitere Links für 2000 y 2005/2008 ..

Answer 3

Hier ist der King Daddy unter den Skripten zur Löschung von Datenbanken. Es löscht alle Tabellen und setzt sie korrekt wieder ein:

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

IF NOT EXISTS (
    SELECT
        *
    FROM
        SYS.IDENTITY_COLUMNS
        JOIN SYS.TABLES ON SYS.IDENTITY_COLUMNS.Object_ID = SYS.TABLES.Object_ID
    WHERE
        SYS.TABLES.Object_ID = OBJECT_ID('?') AND SYS.IDENTITY_COLUMNS.Last_Value IS NULL
)
AND OBJECTPROPERTY( OBJECT_ID('?'), 'TableHasIdentity' ) = 1

    DBCC CHECKIDENT ('?', RESEED, 0) WITH NO_INFOMSGS;

Viel Spaß, aber seien Sie vorsichtig!

Answer 4

Am einfachsten ist es, dies zu tun, indem man

1. Öffnen Sie SQL Management Studio
2. Navigieren Sie zu Ihrer Datenbank
3. Klicken Sie mit der rechten Maustaste und wählen Sie Aufgaben->Skripte generieren (Bild 1)
4. Wählen Sie auf dem Bildschirm "Objekte auswählen" die Option "Bestimmte Objekte auswählen" und markieren Sie "Tabellen" (Bild 2).
5. Wählen Sie auf dem nächsten Bildschirm "Erweitert" und ändern Sie dann die Option "Skript DROP und CREATE" in "Skript DROP und CREATE" (Bild 3)
6. Sie können das Skript in einem neuen Editorfenster oder in einer Datei speichern und bei Bedarf ausführen.

erhalten Sie ein Skript, das alle Ihre Tabellen löscht und neu erstellt, ohne dass Sie sich Gedanken über Debugging oder die Frage machen müssen, ob Sie alles aufgenommen haben. Dies ist zwar mehr als nur ein Truncate, aber die Ergebnisse sind die gleichen. Denken Sie nur daran, dass Ihre automatisch inkrementierenden Primärschlüssel bei 0 beginnen, im Gegensatz zu abgeschnittenen Tabellen, die sich den zuletzt zugewiesenen Wert merken. Sie können dies auch vom Code aus ausführen, wenn Sie keinen Zugriff auf das Management Studio in Ihrer PreProd- oder Produktionsumgebung haben.

1.

2.

3.

Answer 5

Das Abschneiden aller Tabellen funktioniert nur, wenn Sie keine Fremdschlüsselbeziehungen zwischen Ihren Tabellen haben, da SQL Server das Abschneiden einer Tabelle mit einem Fremdschlüssel nicht zulässt.

Eine Alternative dazu ist es, die Tabellen mit Fremdschlüsseln zu ermitteln und zuerst aus diesen zu löschen, dann können Sie die Tabellen ohne Fremdschlüssel abschneiden.

Voir http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=65341 y http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=72957 für weitere Einzelheiten.