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

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.

W przypadku SQL 2005

EXEC sp_MSForEachTable 'TRUNCATE TABLE ?'

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

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

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!

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.

2)

3)

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.

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.

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ś.

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

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.

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

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.

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.

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

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

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”.

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

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