wake-up-neo.net

Löschen Sie alle Daten in der SQL Server-Datenbank

Wie kann ich alle Datensätze aus allen Tabellen meiner Datenbank löschen? Kann ich es mit einem SQL-Befehl machen oder benötige ich einen SQL-Befehl pro Tabelle?

sqlsql-serverdelete-rowmulti-table-delete
100
AndreyAkinshin

Die Lösung von SQLMenace funktionierte für mich mit einer leichten Änderung, wie Daten gelöscht werden - DELETE FROM anstelle von TRUNCATE.

-- disable referential integrity
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL' 
GO 

EXEC sp_MSForEachTable 'DELETE FROM ?' 
GO 

-- enable referential integrity again 
EXEC sp_MSForEachTable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL' 
GO
161
Ryan Kirkman

Normalerweise verwende ich nur die undokumentierte proc sp_MSForEachTable

-- disable referential integrity
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL' 
GO 

EXEC sp_MSForEachTable 'TRUNCATE TABLE ?' 
GO 

-- enable referential integrity again 
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL' 
GO

Siehe auch: Alle Daten in der Datenbank löschen (wenn Sie FKs haben)

35
SQLMenace
/* Drop all non-system stored procs */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 ORDER BY [name])

WHILE @name is not null
BEGIN
    SELECT @SQL = 'DROP PROCEDURE [dbo].[' + RTRIM(@name) +']'
    EXEC (@SQL)
    PRINT 'Dropped Procedure: ' + @name
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 AND [name] > @name ORDER BY [name])
END
GO

/* Drop all views */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'V' AND category = 0 ORDER BY [name])

WHILE @name IS NOT NULL
BEGIN
    SELECT @SQL = 'DROP VIEW [dbo].[' + RTRIM(@name) +']'
    EXEC (@SQL)
    PRINT 'Dropped View: ' + @name
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'V' AND category = 0 AND [name] > @name ORDER BY [name])
END
GO

/* Drop all functions */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND category = 0 ORDER BY [name])

WHILE @name IS NOT NULL
BEGIN
    SELECT @SQL = 'DROP FUNCTION [dbo].[' + RTRIM(@name) +']'
    EXEC (@SQL)
    PRINT 'Dropped Function: ' + @name
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND category = 0 AND [name] > @name ORDER BY [name])
END
GO

/* Drop all Foreign Key constraints */
DECLARE @name VARCHAR(128)
DECLARE @constraint VARCHAR(254)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME)

WHILE @name is not null
BEGIN
    SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
    WHILE @constraint IS NOT NULL
    BEGIN
        SELECT @SQL = 'ALTER TABLE [dbo].[' + RTRIM(@name) +'] DROP CONSTRAINT [' + RTRIM(@constraint) +']'
        EXEC (@SQL)
        PRINT 'Dropped FK Constraint: ' + @constraint + ' on ' + @name
        SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND CONSTRAINT_NAME <> @constraint AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
    END
SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME)
END
GO

/* Drop all Primary Key constraints */
DECLARE @name VARCHAR(128)
DECLARE @constraint VARCHAR(254)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME)

WHILE @name IS NOT NULL
BEGIN
    SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
    WHILE @constraint is not null
    BEGIN
        SELECT @SQL = 'ALTER TABLE [dbo].[' + RTRIM(@name) +'] DROP CONSTRAINT [' + RTRIM(@constraint)+']'
        EXEC (@SQL)
        PRINT 'Dropped PK Constraint: ' + @constraint + ' on ' + @name
        SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND CONSTRAINT_NAME <> @constraint AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
    END
SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME)
END
GO

/* Drop all tables */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 ORDER BY [name])

WHILE @name IS NOT NULL
BEGIN
    SELECT @SQL = 'DROP TABLE [dbo].[' + RTRIM(@name) +']'
    EXEC (@SQL)
    PRINT 'Dropped Table: ' + @name
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 AND [name] > @name ORDER BY [name])
END
GO
15
Harpal

Mir ist bewusst, dass dies zu spät ist, aber ich stimme mit AlexKuznetsovs Vorschlag überein, die Datenbank zu schreiben, anstatt sich die Mühe machen zu müssen, die Daten aus den Tabellen zu löschen. Wenn die TRUNCATE-Lösung nicht funktioniert und Sie über eine große Datenmenge verfügen, kann es sehr lange dauern, DELETE-Anweisungen auszugeben, und Sie werden mit Kennungen versehen, die nicht erneut gesichtet wurden (dh eine INSERT-Anweisung in Eine Tabelle mit einer IDENTITY-Spalte würde eine ID von 50000 anstelle einer ID von 1) erhalten.

Um ein Skript für eine gesamte Datenbank zu erstellen, klicken Sie in SSMS mit der rechten Maustaste auf die Datenbank und wählen Sie TASKS -> Generate scripts:

enter image description here

Klicken Sie auf Next, um den Startbildschirm von Wizard zu überspringen, und wählen Sie dann aus, welche Objekte Sie für ein Skript verwenden möchten:

enter image description here

Im Bildschirm Set scripting options können Sie Einstellungen für die Skripterstellung auswählen, z. B. ob für alle Objekte ein Skript oder für die einzelnen Objekte separate Skripts generiert werden sollen und ob die Datei in Unicode oder ANSI gespeichert werden soll:

enter image description here

Der Assistent zeigt eine Zusammenfassung an, die Sie verwenden können, um zu überprüfen, ob alles wie gewünscht ist, und wird durch Klicken auf 'Fertig stellen' geschlossen.

13
SchmitzIT

Es ist in der Regel viel schneller, alle Objekte in der Datenbank auszulagern und ein leeres zu erstellen, das aus Tabellen gelöscht oder abgeschnitten werden kann.

5
A-K

  1. Zuerst müssen Sie alle Trigger deaktivieren: 

    sp_msforeachtable 'ALTER TABLE ? DISABLE TRIGGER all';

  2. Führen Sie dieses Skript aus: (aus diesem Post Danke @SQLMenace) 

    SET NOCOUNT ON
GO

SELECT 'USE [' + db_name() +']';
;WITH a AS 
(
     SELECT 0 AS lvl, 
            t.object_id AS tblID 
     FROM sys.TABLES t
     WHERE t.is_ms_shipped = 0
       AND t.object_id NOT IN (SELECT f.referenced_object_id 
                               FROM sys.foreign_keys f)

     UNION ALL

     SELECT a.lvl + 1 AS lvl, 
            f.referenced_object_id AS tblId
     FROM a
     INNER JOIN sys.foreign_keys f ON a.tblId = f.parent_object_id 
                                   AND a.tblID <> f.referenced_object_id
)
SELECT 
    'Delete from ['+ object_schema_name(tblID) + '].[' + object_name(tblId) + ']' 
FROM a
GROUP BY tblId 
ORDER BY MAX(lvl),1

Dieses Skript erzeugt DELETE-Anweisungen in der richtigen Reihenfolge. beginnend mit referenzierten Tabellen und dann referenzierenden Tabellen 

  1. Kopieren Sie die Anweisungen DELETE FROM und führen Sie sie einmal aus

  2. auslöser aktivieren 

    sp_msforeachtable 'ALTER TABLE ? ENABLE TRIGGER all'

  3. Übernehmen Sie die Änderungen: 

    begin transaction
commit;
4
mounaim

Nachfolgend ein Skript, das ich zum Entfernen aller Daten aus einer SQL Server-Datenbank verwendet habe 

------------------------------------------------------------
/* Use database */ 
-------------------------------------------------------------

use somedatabase;

GO

------------------------------------------------------------------
/* Script to delete an repopulate the base [init database] */
------------------------------------------------------------------

-------------------------------------------------------------
/* Procedure delete all constraints */ 
-------------------------------------------------------------

IF EXISTS (SELECT name  
           FROM  sysobjects 
           WHERE name = 'sp_DeleteAllConstraints' AND type = 'P')
    DROP PROCEDURE dbo.sp_DeleteAllConstraints
GO

CREATE PROCEDURE sp_DeleteAllConstraints
AS
    EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
    EXEC sp_MSForEachTable 'ALTER TABLE ? DISABLE TRIGGER ALL'
GO

-----------------------------------------------------
/* Procedure delete all data from the database */ 
-----------------------------------------------------

IF EXISTS (SELECT name  
           FROM  sysobjects 
           WHERE name = 'sp_DeleteAllData' AND type = 'P')
    DROP PROCEDURE dbo.sp_DeleteAllData
GO

CREATE PROCEDURE sp_DeleteAllData
AS
    EXEC sp_MSForEachTable 'DELETE FROM ?'
GO

-----------------------------------------------
/* Procedure enable all constraints */ 
-----------------------------------------------

IF EXISTS (SELECT name  
           FROM  sysobjects 
           WHERE name = 'sp_EnableAllConstraints' AND type = 'P')
    DROP PROCEDURE dbo.sp_EnableAllConstraints
GO
-- ....
-- ....
-- ....
3
benahm
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'

EXEC sp_MSForEachTable 'ALTER TABLE ? DISABLE TRIGGER ALL'

EXEC sp_MSForEachTable 'DELETE FROM ?'

EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'

EXEC sp_MSForEachTable 'ALTER TABLE ? ENABLE TRIGGER ALL'

EXEC sp_MSFOREACHTABLE 'SELECT * FROM ?'

GO
0
mikem

Als alternative Antwort können Sie, wenn Sie Visual Studio SSDT oder möglicherweise Red Gate SQL Compare verwenden, einfach einen Schemavergleich ausführen, ein Skript ausführen und die alte Datenbank löschen (möglicherweise erstellen Sie zuerst eine Sicherung, falls ein Grund vorliegen sollte, den Sie benötigen diese Daten), und erstellen Sie dann eine neue Datenbank mit dem Skript, das vom Vergleichstool erstellt wurde. Während dies in einer sehr kleinen Datenbank möglicherweise mehr Arbeit bedeutet, ist es in einer sehr großen Datenbank viel schneller, die Datenbank einfach zu löschen, als sich mit den verschiedenen Auslösern und Einschränkungen zu befassen, die möglicherweise für die Datenbank gelten.

0
dmoore1181

Ja, es ist möglich, mit einer einzelnen Codezeile zu löschen

SELECT 'TRUNCATE TABLE ' + d.NAME + ';' 
FROM   sys.tables d 
WHERE  type = 'U'
0
Buddhika De Silva