Jak obcinać wszystkie tabele w bazie danych przy użyciu TSQL?

Gdy mamy do czynienia z usuwaniem danych z tabel, które mają relacje z kluczami obcymi - co jest w zasadzie w przypadku każdej prawidłowo 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 o wyłączaniu ograniczeń i wyzwalaczy tutaj

Jeśli niektóre z tabel mają kolumny tożsamości, możemy chcieć je ponownie przesłać

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

Zauważ, że zachowanie RESEEDA różni się między nową tabelą, a jedną który wcześniej wstawiał dane z BOL:

DBCC CHECKIDENT ('table_name', RESEED, newReseedValue)

Bieżąca wartość tożsamości jest ustawiona na newReseedValue. Jeśli nie ma wierszy został wstawiony do tabeli, ponieważ został utworzony, pierwszy wiersz wstawiony po wykonaniu DBCC CHECKIDENT będzie użyj newReseedValue jako tożsamości. W przeciwnym razie, następny wiersz wstawiony będzie użyj newReseedValue + 1. Jeżeli wartość z newReseedValue jest mniej niż maksymalna wartość w kolumnie tożsamość, zostanie wygenerowany komunikat o błędzie 2627 na kolejnych odniesieniach do tabeli.

Podziękowania dlaRoberta za zwrócenie uwagi na fakt, że wyłączenie ograniczeń nie pozwala na użycie truncate, ograniczenia trzeba by usunąć, a następnie odtworzyć

Oto król skryptów usuwania baz danych. Wyczyści wszystkie tabele i prześle je poprawnie:

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'

Najprostszym sposobem na to jest

1. Otwórz SQL Management Studio
2. Przejdź do bazy danych
3. Kliknij prawym przyciskiem myszy i wybierz zadania->Generuj Skrypty (pic 1)
4. na ekranie " Wybierz obiekty "wybierz opcję" Wybierz określone obiekty "i zaznacz" tabele " (pic 2)
5. na następnym ekranie wybierz "advanced", a następnie zmień opcję" script DROP and CREATE " na "Script DROP and CREATE" (pic 3)
6. Wybierz, aby zapisać skrypt w nowym oknie edytora lub plik i uruchomić w razie potrzeby.

To daje skrypt, który upuszcza i odtwarza wszystkie tabele bez konieczności martwienia się o debugowanie lub czy uwzględniłeś wszystko. Chociaż wykonuje to więcej niż tylko obcinanie, wyniki są takie same. Pamiętaj tylko, że automatyczne zwiększanie kluczy podstawowych rozpocznie się od 0, w przeciwieństwie do przyciętych tabel, które zapamiętają ostatnią przypisaną wartość. Możesz również wykonać to z kodu, jeśli nie masz dostępu do zarządzania studio na swoim środowisku przedprodukcyjnym lub produkcyjnym.

1.

2.

3.

Obcinanie wszystkich tabel będzie działać tylko wtedy, gdy nie masz żadnych relacji między tabelami z kluczem obcym, ponieważ SQL Server nie pozwoli Ci obcinać tabeli za pomocą klucza obcego.

Alternatywą dla tego jest określenie tabel z kluczami obcymi i usunięcie z nich najpierw, a następnie obcięcie tabel bez kluczy obcych.

Zobacz http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=65341 oraz http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=72957 Po Więcej 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! To nie jest dobry pomysł.

Jeśli wiesz, które tabele chcesz obciąć, Utwórz procedurę składowaną, która je obcina. Możesz naprawić zamówienie, aby uniknąć problemów z kluczem obcym.

Jeśli naprawdę chcesz je wszystkie obciąć (tak, że można BCP załadować je na przykład) można byłoby równie szybko upuścić bazę danych i utworzyć nową od zera, co miałoby dodatkową zaletę, że wiesz dokładnie, gdzie jesteś.

Jeśli chcesz zachować dane w określonej tabeli (np. statycznej tabeli wyszukiwania) podczas usuwania / obcinania danych w innych tabelach w tym samym db, potrzebujesz pętli z wyjątkami w niej. Tego właśnie szukałem, gdy natknąłem się na to pytanie.

Sp_MSForEachTable wydaje mi się błędny (tj. niespójne zachowanie ze stwierdzeniami IF) i prawdopodobnie dlatego jego nieudokumentowane 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

Zrób pustą bazę "szablonów", zrób pełną kopię zapasową. Gdy chcesz odświeżyć, po prostu Przywróć za pomocą REPLACE. Szybki, prosty, kuloodporny. A jeśli kilka tabel tutaj lub tam potrzebuje podstawowych danych (np. informacji o konfiguracji lub po prostu podstawowych informacji, które sprawiają, że aplikacja działa), obsługuje to zbyt.

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

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

Poniższe zapytanie tworzy instrukcje drop & create dla każdego ograniczenia odnoszącego się do każdej nazwy tabeli w @myTempTable. Jeśli chcesz wygenerować je dla wszystkich tabel, możesz użyć schematu informacyjnego, aby zebrać te 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 programisty możesz użyć kursora, aby je uruchomić dynamicznie.

To jest Jeden sposób, aby to zrobić... prawdopodobnie jest 10 innych, które są lepsze / bardziej wydajne, ale brzmi to tak, jakby robiono to bardzo rzadko, więc idzie...

Pobierz listę tables z sysobjects, a następnie zapętl je kursorem, wywołując sp_execsql('truncate table ' + @table_name) dla każdego iteration.

Nie rozumiem, dlaczego wyczyszczenie danych byłoby lepsze niż skrypt do upuszczania i ponownego tworzenia każdej tabeli.

To lub zachowaj kopię zapasową pustego DB i przywróć go nad starym

Przed obcięciem tabel należy usunąć wszystkie klucze obce. Użyj tego skryptu , aby wygenerować Skrypty końcowe, aby upuścić i odtworzyć wszystkie klucze obce w bazie danych. Proszę ustawić zmienną @ action na 'CREATE' lub 'DROP'.

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

Możesz to zmodyfikować, jeśli chcesz robić wszystkie tabele, wystarczy wpisać SELECT name do # TruncateList z sys.stoły. Jednak zazwyczaj 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 to zbyt tępe jak na twoje podanie. To nie dla 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

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

1. Przechowuj wszystkie ograniczenia w tymczasowej tabeli
2. Porzuć Wszystkie Ograniczenia
3. obcinaj wszystkie tabele z wyjątkiem niektórych tabel, które nie wymagają obcinania
4. odtworzyć wszystkie ograniczenia.

Umieściłem go na moim blogu tutaj

Wybierz "delete from" +TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_TYPE= 'BASE TABLE'

Gdzie wynik.

Skopiuj i wklej w oknie zapytania i uruchom polecenie