Jak obciąć wszystkie tabele w bazie danych za pomocą TSQL?


204

Mam środowisko testowe dla bazy danych, którą chcę ponownie załadować z nowymi danymi na początku cyklu testowania. Nie jestem zainteresowany przebudową całej bazy danych - po prostu „resetuję” dane.

Jaki jest najlepszy sposób na usunięcie wszystkich danych ze wszystkich tabel za pomocą TSQL? Czy istnieją procedury systemowe, widoki itp., Które można wykorzystać? Nie chcę ręcznie tworzyć i obsługiwać obcinanych instrukcji tabel dla każdej tabeli - wolałbym, aby była dynamiczna.

Odpowiedzi:


188

W przypadku SQL 2005

EXEC sp_MSForEachTable 'TRUNCATE TABLE ?'

Kilka dodatkowych linków do 2000 i 2005/2008 ..


62
Nie można obcinać tabel, które mają klucze obce, więc zadziała to tylko wtedy, gdy między tabelami nie ma ograniczeń klucza obcego (lub zostały wyłączone).
marcj

1
zgodził się .. myślałem, że skoro specjalnie poprosił o obcięcie tabel, już rozwiązał problem z kluczami obcymi ..
Gulzar Nazim

@ gulzar - tak jakby - napisałem osobne pytanie, jak radzić sobie z FK, ale twoja odpowiedź ma swoje zalety.
Ray

11
@Sam: Nie, nie będzie! Dane w tabelach są nieistotne. Tak długo, jak istnieje ograniczenie klucza obcego odnoszące się do tabeli (nawet wyłączonej), nie można go obciąć.
TToni,

3
„EXEC sp_MSForEachTable” DROP TABLE? ” Działa również świetnie :) (zachwycając wszystkie tabele z bazy danych)
kuncevic.dev

418

Podczas usuwania danych z tabel, które mają powiązania z kluczem obcym - co w zasadzie ma miejsce w przypadku właściwie zaprojektowanej bazy danych - możemy wyłączyć wszystkie ograniczenia, usunąć wszystkie dane, a następnie ponownie włączyć ograniczenia

-- 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"

Więcej informacji na temat wyłączania ograniczeń i wyzwalaczy tutaj

jeśli niektóre tabele mają kolumny tożsamości, możemy chcieć je ponownie załadować

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

Zauważ, że zachowanie RESEED różni się w zależności od nowej tabeli i tabeli, w której wcześniej wstawiono niektóre dane z BOL :

DBCC CHECKIDENT („nazwa_tabeli”, RESEED, newReseedValue)

Bieżąca wartość tożsamości jest ustawiona na newReseedValue. Jeśli do tabeli nie wstawiono żadnych wierszy od czasu jej utworzenia, pierwszy wiersz wstawiony po wykonaniu DBCC CHECKIDENT użyje newReseedValue jako tożsamości. W przeciwnym razie następny wstawiony wiersz użyje wartości newReseedValue + 1. Jeśli wartość newReseedValue jest mniejsza niż maksymalna wartość w kolumnie tożsamości, komunikat o błędzie 2627 zostanie wygenerowany przy kolejnych odwołaniach do tabeli.

Dzięki Robertowi za zwrócenie uwagi na fakt, że wyłączenie ograniczeń nie pozwala na użycie obcięcia, ograniczenia musiałyby zostać usunięte, a następnie odtworzone


34
Wyłączenie ograniczeń NIE pozwoli na obcięcie tabel, do których odnosi się ograniczenie KLUCZ OBCY. Ograniczenie FK musi zostać usunięte. Proszę o odpowiedź, jeśli się mylę, ale nie znalazłem sposobu, aby uniknąć ich upuszczenia.
Robert Claypool,

1
Po prostu literówka słowa kluczowego „Tabela” nie powinna być w tej instrukcji EXEC sp_MSForEachTable „USUNĄĆ Z TABELI?” Prawidłowa wersja powinna być: EXEC sp_MSForEachTable „USUNĄĆ Z?”
Raghav

4
Jeśli używasz SSMS w wersji 2008 lub nowszej, prawdopodobnie będziesz chciał dodać SET ROWCOUNT 0na początku skryptu, ponieważ domyślnie jest to ograniczenie akcji do 500 wierszy! Dostaniesz frustrujące błędy, tak jak ja, ponieważ nie wszystkie dane zostaną faktycznie usunięte.
Sean Hanley,

1
To działało świetnie. W moim przypadku musiałem również dodać EXEC sp_msforeachtable „ALTER TABLE? Wyłącz TRIGGER all” i EXEC sp_msforeachtable? ALTER TABLE? Włącz TRIGGER all? Przed i po instrukcji usuwania.
RobC

2
Moja ulubiona odpowiedź. Ale dlaczego (wszyscy, nawet komentujący) umieszczasz dosłowne ciągi SQL w cudzysłowach?
bitoolean

57

Oto królewski tata skryptów do czyszczenia baz danych. Spowoduje to wyczyszczenie wszystkich tabel i prawidłowe ich ponowne załadowanie:

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;

Ciesz się, ale bądź ostrożny!


2
Niestety powyższe polecenie kończy się niepowodzeniem, jeśli masz wyliczone kolumny, ponieważ sp_MSforeachtable najwyraźniej ma SET QUOTED_IDENTITY OFFw swoim ciele ( link ). AKTUALIZACJA: Poprawka polega na dodaniu „SET QUOTED_IDENTIFIERS on;” na początku każdego stwierdzenia, które powoduje ten błąd (jak wspomniano tutaj )
Marchy,

1
wygląda na to, że to nie spowodowało
ponownego wyzerowania

48

Najprostszym sposobem na to jest

  1. otwórz SQL Management Studio
  2. przejdź do swojej bazy danych
  3. Kliknij prawym przyciskiem myszy i wybierz Zadania-> Generuj skrypty (rys. 1)
  4. Na ekranie „wybierz obiekty” wybierz opcję „wybierz określone obiekty” i zaznacz „tabele” (rys. 2)
  5. na następnym ekranie wybierz „zaawansowane”, a następnie zmień opcję „Skrypt DROP i UTWÓRZ” na „Skrypt DROP i UTWÓRZ” (rys. 3)
  6. Wybierz, aby zapisać skrypt w nowym oknie edytora lub pliku i uruchomić w razie potrzeby.

da ci to skrypt, który upuszcza i odtwarza wszystkie tabele bez potrzeby martwienia się o debugowanie lub o to, czy wszystko załączyłeś. Chociaż wykonuje to więcej niż tylko obcinanie, wyniki są takie same. Pamiętaj tylko, że automatycznie zwiększające się klucze podstawowe zaczną od 0, w przeciwieństwie do skróconych tabel, które zapamiętują ostatnią przypisaną wartość. Możesz to również wykonać z kodu, jeśli nie masz dostępu do Management Studio w środowisku PreProd lub produkcyjnym.

1.

wprowadź opis zdjęcia tutaj

2)

wprowadź opis zdjęcia tutaj

3)

wprowadź opis zdjęcia tutaj


1
Ostrożnie, jeśli używasz tego do bazy danych o bardzo złożonym schemacie. Wypróbowałem go na kopii deweloperskiej naszej produkcyjnej bazy danych i zniszczył schemat, wymagając całkowitej ponownej instalacji.
Techrocket9 27.04.17

1
Musisz napisać scenariusz do wszystkich rzeczy, które chcesz zachować
Kapitan Kenpachi

13

Obcinanie wszystkich tabel będzie działać tylko wtedy, gdy nie będzie żadnych relacji między kluczami obcymi między tabelami, ponieważ SQL Server nie pozwoli na obcięcie tabeli za pomocą klucza obcego.

Alternatywą jest określenie tabel z kluczami obcymi i usunięcie z nich najpierw, a następnie można obciąć tabele bez kluczy obcych.

Zobacz http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=65341 i http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=72957 dla dalszych szczegółów.


1
Słuszna uwaga. Nie myślałem o tym. Być może będę mógł najpierw wyłączyć wszystkie ograniczenia, a następnie włączyć je ponownie po usunięciu danych.
Ray

7

Alternatywną opcją, którą lubię używać z MSSQL Server Deveploper lub Enterprise, jest utworzenie migawki bazy danych natychmiast po utworzeniu pustego schematu. W tym momencie możesz po prostu przywracać bazę danych z powrotem do migawki.


Niestety za każdym razem tracisz wszystkie indeksy FULLTEXT
Chris KL

6

Nie rób tego! Naprawdę niezbyt dobry pomysł.

Jeśli wiesz, które tabele chcesz obciąć, utwórz procedurę składowaną, która je obetnie. Możesz ustalić kolejność, aby uniknąć problemów z kluczem obcym.

Jeśli naprawdę chcesz je obciąć (aby na przykład załadować je BCP), równie szybko możesz usunąć bazę danych i utworzyć nową od podstaw, co miałoby dodatkową korzyść, którą wiesz dokładnie, gdzie jesteś.


Ładne alternatywne podejście tutaj.
Sam

3
problem z twoim podejściem polega na tym, że upuszczenie tabel i bazy danych spowoduje utratę wszystkich uprawnień, które otrzymano dla różnych loginów i schematów. Odtworzenie tego będzie bolesne dla dużych baz danych z dużą ilością tabel.
Punit Vora

4

Jeśli chcesz zachować dane w określonej tabeli (tj. Statycznej tabeli wyszukiwania), jednocześnie usuwając / obcinając dane w innych tabelach w tym samym pliku db, potrzebujesz pętli z wyjątkami. Właśnie tego szukałem, gdy natknąłem się na to pytanie.

sp_MSForEachTable wydaje mi się błędny (tj. niespójne zachowanie z instrukcjami IF) i prawdopodobnie dlatego jest nieudokumentowany przez 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

4

Najtrudniejszą częścią obcinania wszystkich tabel jest usunięcie i ponowne dopasowanie ograniczeń klucza obcego.

Poniższe zapytanie tworzy instrukcje drop & create dla każdego ograniczenia związanego z nazwą każdej tabeli w @myTempTable. Jeśli chcesz wygenerować je dla wszystkich tabel, możesz po prostu użyć schematu informacji, aby zamiast tego zebrać nazwy tabel.

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)

Następnie po prostu kopiuję instrukcje do uruchomienia - ale przy odrobinie wysiłku dewelopera możesz użyć kursora, aby uruchomić je dynamicznie.


3

Znacznie łatwiej (a być może nawet szybciej) wykasować bazę danych, a następnie po prostu upuścić i utworzyć ją ze skryptu.


3

Utwórz pustą bazę „szablonów”, zrób pełną kopię zapasową. Kiedy musisz odświeżyć, po prostu przywróć za pomocą Z WYMIANY. Szybki, prosty, kuloodporny. A jeśli kilka tabel tutaj lub tam potrzebuje pewnych danych podstawowych (np. Informacji o konfiguracji lub tylko podstawowych informacji, które powodują, że aplikacja działa), to również to obsługuje.


2

Jest to jeden ze sposobów, aby to zrobić ... prawdopodobnie jest 10 innych, które są lepsze / bardziej wydajne, ale wygląda na to, że robi się to bardzo rzadko, więc proszę ...

uzyskaj listę tablesod sysobjects, a następnie przewiń kursorem kursor, wywołując sp_execsql('truncate table ' + @table_name)każdą z nich iteration.


dodałem post z sql, który właśnie to robi :), ponieważ tego też szukałem.
Chris Smith,

1

Uruchom raz komentowaną sekcję, zapełnij tabelę _TruncateList tabelami, które chcesz obciąć, a następnie uruchom resztę skryptu. Tabela _ScriptLog będzie musiała zostać z czasem wyczyszczona, jeśli często to robisz.

Możesz to zmienić, jeśli chcesz wykonać wszystkie tabele, po prostu wpisz WYBIERZ nazwę DO #TruncateList FROM sys.tables. Zazwyczaj jednak nie chcesz robić ich wszystkich.

Wpłynie to również na wszystkie klucze obce w bazie danych i możesz to również zmodyfikować, jeśli jest to zbyt tępe dla twojej aplikacji. To nie do moich celów.

/*
CREATE TABLE _ScriptLog 
(
    ID Int NOT NULL Identity(1,1)
    , DateAdded DateTime2 NOT NULL DEFAULT GetDate()
    , Script NVarChar(4000) NOT NULL
)

CREATE UNIQUE CLUSTERED INDEX IX_ScriptLog_DateAdded_ID_U_C ON _ScriptLog
(
    DateAdded
    , ID
)

CREATE TABLE _TruncateList
(
    TableName SysName PRIMARY KEY
)
*/
IF OBJECT_ID('TempDB..#DropFK') IS NOT NULL BEGIN
    DROP TABLE #DropFK
END

IF OBJECT_ID('TempDB..#TruncateList') IS NOT NULL BEGIN
    DROP TABLE #TruncateList
END

IF OBJECT_ID('TempDB..#CreateFK') IS NOT NULL BEGIN
    DROP TABLE #CreateFK
END

SELECT Scripts = 'ALTER TABLE ' + '[' + OBJECT_NAME(f.parent_object_id)+ ']'+
' DROP  CONSTRAINT ' + '[' + f.name  + ']'
INTO #DropFK
FROM .sys.foreign_keys AS f
INNER JOIN .sys.foreign_key_columns AS fc
ON f.OBJECT_ID = fc.constraint_object_id

SELECT TableName
INTO #TruncateList
FROM _TruncateList

SELECT Scripts = 'ALTER TABLE ' + const.parent_obj + '
    ADD CONSTRAINT ' + const.const_name + ' FOREIGN KEY (
            ' + const.parent_col_csv + '
            ) REFERENCES ' + const.ref_obj + '(' + const.ref_col_csv + ')
'
INTO #CreateFK
FROM (
    SELECT QUOTENAME(fk.NAME) AS [const_name]
        ,QUOTENAME(schParent.NAME) + '.' + QUOTENAME(OBJECT_name(fkc.parent_object_id)) AS [parent_obj]
        ,STUFF((
                SELECT ',' + QUOTENAME(COL_NAME(fcP.parent_object_id, fcp.parent_column_id))
                FROM sys.foreign_key_columns AS fcP
                WHERE fcp.constraint_object_id = fk.object_id
                FOR XML path('')
                ), 1, 1, '') AS [parent_col_csv]
        ,QUOTENAME(schRef.NAME) + '.' + QUOTENAME(OBJECT_NAME(fkc.referenced_object_id)) AS [ref_obj]
        ,STUFF((
                SELECT ',' + QUOTENAME(COL_NAME(fcR.referenced_object_id, fcR.referenced_column_id))
                FROM sys.foreign_key_columns AS fcR
                WHERE fcR.constraint_object_id = fk.object_id
                FOR XML path('')
                ), 1, 1, '') AS [ref_col_csv]
    FROM sys.foreign_key_columns AS fkc
    INNER JOIN sys.foreign_keys AS fk ON fk.object_id = fkc.constraint_object_id
    INNER JOIN sys.objects AS oParent ON oParent.object_id = fkc.parent_object_id
    INNER JOIN sys.schemas AS schParent ON schParent.schema_id = oParent.schema_id
    INNER JOIN sys.objects AS oRef ON oRef.object_id = fkc.referenced_object_id
    INNER JOIN sys.schemas AS schRef ON schRef.schema_id = oRef.schema_id
    GROUP BY fkc.parent_object_id
        ,fkc.referenced_object_id
        ,fk.NAME
        ,fk.object_id
        ,schParent.NAME
        ,schRef.NAME
    ) AS const
ORDER BY const.const_name

INSERT INTO _ScriptLog (Script)
SELECT Scripts
FROM #CreateFK

DECLARE @Cmd NVarChar(4000)
    , @TableName SysName

WHILE 0 < (SELECT Count(1) FROM #DropFK) BEGIN
    SELECT TOP 1 @Cmd = Scripts 
    FROM #DropFK

    EXEC (@Cmd)

    DELETE #DropFK WHERE Scripts = @Cmd
END

WHILE 0 < (SELECT Count(1) FROM #TruncateList) BEGIN
    SELECT TOP 1 @Cmd = N'TRUNCATE TABLE ' +  TableName
        , @TableName = TableName
    FROM #TruncateList

    EXEC (@Cmd)

    DELETE #TruncateList WHERE TableName = @TableName
END

WHILE 0 < (SELECT Count(1) FROM #CreateFK) BEGIN
    SELECT TOP 1 @Cmd = Scripts 
    FROM #CreateFK

    EXEC (@Cmd)

    DELETE #CreateFK WHERE Scripts = @Cmd
END

0

Nie rozumiem, dlaczego czyszczenie danych byłoby lepsze niż skrypt, aby upuścić i ponownie utworzyć każdą tabelę.

To lub zachowaj kopię zapasową pustej bazy danych i przywróć ją do starej


2
Powodem jest to, że narzut związany z upuszczaniem i odtwarzaniem plików na dysku, dzienników itp. Bazy danych jest bardzo powolny. Pomyśl o wyczyszczeniu bazy danych 1000 razy podczas przyzwoitego testu jednostkowego.
Chris KL

0

Przed obcięciem tabel musisz usunąć wszystkie klucze obce. Ten skrypt służy do generowania ostatecznych skryptów do upuszczania i ponownego tworzenia wszystkich kluczy obcych w bazie danych. Ustaw zmienną @action na „CREATE” lub „DROP”.


0

wybierz „usuń z” + TABLE_NAME z INFORMACJE_SCHEMA.TABLES gdzie TABLE_TYPE = „BASE TABLE”

skąd wynik.

Skopiuj i wklej w oknie zapytania i uruchom polecenie


0

Jest trochę późno, ale może komuś pomóc. Czasami utworzyłem procedurę, która wykonuje następujące czynności przy użyciu T-SQL:

  1. Przechowuj wszystkie ograniczenia w tabeli tymczasowej
  2. Porzuć wszystkie ograniczenia
  3. Obetnij wszystkie tabele, z wyjątkiem niektórych tabel, które nie wymagają obcięcia
  4. Odtwórz wszystkie ograniczenia.

Umieściłem go tutaj na moim blogu

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.