Usuń wszystkie dane z bazy danych SQL Server


Odpowiedzi:


194

Rozwiązanie SQLMenace zadziałało dla mnie z niewielkimi zmianami w sposobie usuwania danych - DELETE FROMzamiast 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

Me To… Udało mi się usunąć, ale nie obcinać.
Marcel

17
Może również mieć sens zrobienie a EXEC sp_MSForEachTable 'DBCC CHECKIDENT(''?'', RESEED, 0)'po DELETE FROM, aby zresetować wszystkie kolumny tożsamości z powrotem do 0.
Jonathan Amend

1
To zawsze dobry początek dnia, gdy znajdziesz 6 linii kodu, które zastępują setki instrukcji delete! Ta metoda działa bez problemu w SQL 2014 Express.
Tommy

1
Nie zapomnij również wyłączyć wyzwalaczy
Edwin Stoteler

10
Otrzymałem błąd - DELETE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'.... U mnie pracowało:EXEC sp_MSForEachTable 'SET QUOTED_IDENTIFIER ON; DELETE FROM ?'
kasi

36

Zwykle będę po prostu używał nieudokumentowanego 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

Zobacz też: Usuń wszystkie dane w bazie danych (jeśli masz ZK)


1
Myślę, że to nie działa. Wygląda na to, że Kalen Delaney był nieumyślnie odpowiedzialny za rozpoczęcie tego pomysłu. Tutaj wyjaśnia, że „musisz porzucić ograniczenie odwołujące się, aby skrócić tabelę”.
Martin Smith

Martin właśnie uruchomiłem to 2 sekundy temu w bazie danych Adventureworks bez problemu
SQLMenace

Na pewno mi się to tutaj nie udaje. create database testing; GO use testing; create table t1 (i int primary key) create table t2(i int primary key,p int references t1)
Martin Smith

2
To nie działa, mimo że jest oznaczony jako odpowiedź. Ustawienie ograniczenia nocheck dla kluczy obcych nie pozwala na wykonywanie poleceń obcięcia na tych tabelach. Nadal będziesz otrzymywać błędy, które uniemożliwiają obcięcie.
Czwarty

3
to nie działa z powodu obecności kluczy obcych. Nadal nie rozumiem, dlaczego została przyjęta jako odpowiedź: /
mounaim

19
/* 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

interesujący skrypt, który nie korzysta z nieskoumentowanego przechowywanego proc 'sp_MSForEachTable', którego brakuje na Azure. Wymaga jednak poprawek, jeśli masz obiekty na innym schemacie niż [dbo].
Pac0

Użyj gist.github.com/metaskills/893599, aby utworzyć sp_MSForEachTable na platformie Azure
Harpal,

16

Zdaję sobie sprawę, że to późno, ale zgadzam się z sugestią AlexKuznetsova, aby napisać skrypt bazy danych, zamiast przechodzić przez kłopoty z czyszczeniem danych z tabel. Jeśli TRUNCATErozwiązanie nie zadziała i zdarzy się, że masz dużą ilość danych, wydawanie (rejestrowanych) DELETEwyciągów może zająć dużo czasu i pozostaną z identyfikatorami, które nie zostały ponownie wprowadzone (tj. INSERTInstrukcja do tabeli z IDENTITYkolumna dostanie ci ID 50000 zamiast identyfikatora z 1).

Aby wykonać skrypt całej bazy danych, w programie SSMS kliknij bazę danych prawym przyciskiem myszy, a następnie wybierz TASKS-> Generate scripts:

wprowadź opis obrazu tutaj

Kliknij, Nextaby pominąć ekran otwierający kreatora, a następnie wybierz obiekty, które chcesz skryptować:

wprowadź opis obrazu tutaj

w Set scripting options ekranie możesz wybrać ustawienia skryptów, takie jak generowanie 1 skryptu dla wszystkich obiektów lub oddzielne skrypty dla poszczególnych obiektów oraz zapisywanie pliku w Unicode lub ANSI:

wprowadź opis obrazu tutaj

Kreator wyświetli podsumowanie, którego możesz użyć, aby sprawdzić, czy wszystko jest zgodne z oczekiwaniami, i zamknij, klikając „Zakończ”.


Uważaj, w ten sposób domyślnie utracisz takie rzeczy, jak indeksy, jeśli nie przejdziesz do przycisku „Zaawansowane”.
glautrou

6
  1. Najpierw musisz wyłączyć wszystkie wyzwalacze:

    sp_msforeachtable 'ALTER TABLE ? DISABLE TRIGGER all';
  2. Uruchom ten skrypt: (Zaczerpnięte z tego postu Dziękuję @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

Ten skrypt wyprodukuje DELETE instrukcje w odpowiedniej kolejności. zaczynając od tabel, do których istnieją odniesienia, a następnie odwołując się do tabel

  1. Skopiuj DELETE FROMinstrukcje i uruchom je raz

  2. włącz wyzwalacze

    sp_msforeachtable 'ALTER TABLE ? ENABLE TRIGGER all'
  3. Zatwierdź zmiany:

    begin transaction
    commit;

Dla mnie to nie działa, zapytanie rekurencyjne kończy się w pętli. Być może z powodu czci dla siebie.
Edwin Stoteler

5

Zwykle znacznie szybciej jest skryptowanie wszystkich obiektów w bazie danych i tworzenie pustego, który służy do usuwania lub obcinania tabel.


3

Poniżej skrypt, którego użyłem do usunięcia wszystkich danych z bazy danych SQL Server

------------------------------------------------------------
/* 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
-- ....
-- ....
-- ....

1
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

Alternatywną odpowiedzią jest to, że jeśli korzystasz z Visual Studio SSDT lub ewentualnie Red Gate Sql Compare, możesz po prostu uruchomić porównanie schematu, wykonać skrypt, usunąć starą bazę danych (prawdopodobnie najpierw wykonaj kopię zapasową na wypadek, gdybyś potrzebował dane), a następnie utwórz nową bazę danych za pomocą skryptu utworzonego przez narzędzie porównawcze. Podczas gdy w przypadku bardzo małej bazy danych może to wymagać więcej pracy, w przypadku bardzo dużej bazy danych znacznie szybciej będzie po prostu usunąć bazę danych, a następnie poradzić sobie z różnymi wyzwalaczami i ograniczeniami, które mogą występować w bazie danych.


-1

Tak, możliwe jest usunięcie za pomocą jednej linii kodu

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

W ten sposób otrzymam nową tabelę z obciętymi instrukcjami dla każdej tabeli. W rzeczywistości niczego nie usuwa i niestety najpierw rozwiązuje problem zrzucania ograniczeń. Szkoda, liczyłem na taką odpowiedź, bez użycia sp_MSForEachTable (który dla mnie nie istnieje, Azure SQL Server)!
Pac0

tak. prawdziwe. tworzy obcinający skrypt dla wszystkich tabel. Użyj tego skryptu, aby usunąć dane z tabel.
Buddhika De Silva

To rozwiązanie działa tylko w przypadku, gdy nie ma żadnych relacji, ponieważ nie gwarantuje, że tabele zostaną usunięte we właściwej kolejności. Ponadto, jeśli istnieją jakiekolwiek czynniki uruchamiające usuwanie danych, może to mieć niezamierzone konsekwencje.
dmoore1181
Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.