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

Tun Sie das nicht! Wirklich, keine gute Idee.

Wenn Sie wissen, welche Tabellen Sie abschneiden wollen, erstellen Sie eine gespeicherte Prozedur, die sie abschneidet. Sie können die Reihenfolge festlegen, um Fremdschlüsselprobleme zu vermeiden.

Wenn Sie wirklich alle Daten abschneiden wollen (z. B. um sie per BCP zu laden), können Sie die Datenbank genauso schnell löschen und eine neue erstellen, was den zusätzlichen Vorteil hat, dass Sie genau wissen, wo Sie sind.

Answer 2

Eine alternative Option, die ich gerne mit MSSQL Server Deveploper oder Enterprise verwende, ist die Erstellung eines Snapshots der Datenbank unmittelbar nach der Erstellung des leeren Schemas. Dann können Sie die Datenbank einfach immer wieder auf den Snapshot zurücksetzen.

Answer 3

Der schwierigste Teil des Abschneidens aller Tabellen ist das Entfernen und erneute Laden der Fremdschlüssel-Beschränkungen.

Die folgende Abfrage erstellt die Drop & Create-Anweisungen für jede Einschränkung, die sich auf jeden Tabellennamen in @myTempTable bezieht. Wenn Sie diese für alle Tabellen erstellen möchten, können Sie stattdessen einfach das Informationsschema verwenden, um diese Tabellennamen zu sammeln.

DECLARE @myTempTable TABLE (tableName varchar(200))
INSERT INTO @myTempTable(tableName) VALUES
('TABLE_ONE'),
('TABLE_TWO'),
('TABLE_THREE')

-- DROP FK Contraints
SELECT 'alter table '+quotename(schema_name(ob.schema_id))+
  '.'+quotename(object_name(ob.object_id))+ ' drop constraint ' + quotename(fk.name) 
  FROM sys.objects ob INNER JOIN sys.foreign_keys fk ON fk.parent_object_id = ob.object_id
  WHERE fk.referenced_object_id IN 
      (
         SELECT so.object_id 
         FROM sys.objects so JOIN sys.schemas sc
         ON so.schema_id = sc.schema_id
         WHERE so.name IN (SELECT * FROM @myTempTable)  AND sc.name=N'dbo'  AND type in (N'U'))

 -- CREATE FK Contraints
 SELECT 'ALTER TABLE [PIMSUser].[dbo].[' +cast(c.name as varchar(255)) + '] WITH NOCHECK ADD CONSTRAINT ['+ cast(f.name as varchar(255)) +'] FOREIGN KEY (['+ cast(fc.name as varchar(255)) +'])
      REFERENCES [PIMSUser].[dbo].['+ cast(p.name as varchar(255)) +'] (['+cast(rc.name as varchar(255))+'])'
FROM  sysobjects f
      INNER JOIN sys.sysobjects c ON f.parent_obj = c.id
      INNER JOIN sys.sysreferences r ON f.id = r.constid
      INNER JOIN sys.sysobjects p ON r.rkeyid = p.id
      INNER JOIN sys.syscolumns rc ON r.rkeyid = rc.id and r.rkey1 = rc.colid
      INNER JOIN sys.syscolumns fc ON r.fkeyid = fc.id and r.fkey1 = fc.colid
WHERE 
      f.type = 'F'
      AND
      cast(p.name as varchar(255)) IN (SELECT * FROM @myTempTable)

Ich kopiere dann einfach die auszuführenden Anweisungen heraus - aber mit ein wenig Entwicklungsaufwand könnten Sie einen Cursor verwenden, um sie dynamisch auszuführen.

Answer 4

Wenn Sie Daten in einer bestimmten Tabelle (z.B. einer statischen Nachschlagetabelle) behalten wollen, während Sie Daten in anderen Tabellen derselben Datenbank löschen/abschneiden, benötigen Sie eine Schleife mit den Ausnahmen darin. Genau danach habe ich gesucht, als ich auf diese Frage gestoßen bin.

sp_MSForEachTable scheint mir fehlerhaft (d.h. inkonsistentes Verhalten mit IF-Anweisungen), die wahrscheinlich ist, warum seine undokumentiert von MS.

declare @LastObjectID int = 0
declare @TableName nvarchar(100) = ''
set @LastObjectID = (select top 1 [object_id] from sys.tables where [object_id] > @LastObjectID order by [object_id])
while(@LastObjectID is not null)
begin
    set @TableName = (select top 1 [name] from sys.tables where [object_id] = @LastObjectID)

    if(@TableName not in ('Profiles', 'ClientDetails', 'Addresses', 'AgentDetails', 'ChainCodes', 'VendorDetails'))
    begin
        exec('truncate table [' + @TableName + ']')
    end 

    set @LastObjectID = (select top 1 [object_id] from sys.tables where [object_id] > @LastObjectID order by [object_id])
end

Answer 5

Erstellen Sie eine leere "Vorlagen"-Datenbank und machen Sie eine vollständige Sicherung. Wenn Sie eine Aktualisierung benötigen, stellen Sie sie einfach mit WITH REPLACE wieder her. Schnell, einfach, kugelsicher. Und wenn ein paar Tabellen hier oder da einige Basisdaten benötigen (z. B. Konfigurationsinformationen oder einfach nur grundlegende Informationen, die Ihre Anwendung zum Laufen bringen), kümmert sich das Programm auch darum.