Jak usunąć wszystkie tabele z bazy danych SQL Server?


195

Próbuję napisać skrypt, który całkowicie opróżni bazę danych SQL Server. Oto co mam do tej pory:

USE [dbname]
GO
EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all'
EXEC sp_msforeachtable 'DELETE ?'

Po uruchomieniu w Management Studio otrzymuję:

Polecenia zostały wykonane pomyślnie.

ale gdy odświeżę listę tabel, wszystkie nadal tam są. Co ja robię źle?


Jeśli żadne z rozwiązań na tej stronie nie działa, być może zapomniałeś: USE [nazwa_bazy_danych] GO
Serj Sagan

2
USUNĄĆ ? usunie rekordy z tabeli. Powinieneś używać DROP TABLE ?, ale to nie zadziała z innych powodów.
datagod

Odpowiedzi:


306

To też nie działa, gdy istnieje wiele tabel kluczy obcych.
Znalazłem ten kod, który działa i robi wszystko, co próbujesz (usuń wszystkie tabele z bazy danych):

DECLARE @Sql NVARCHAR(500) DECLARE @Cursor CURSOR

SET @Cursor = CURSOR FAST_FORWARD FOR
SELECT DISTINCT sql = 'ALTER TABLE [' + tc2.TABLE_SCHEMA + '].[' +  tc2.TABLE_NAME + '] DROP [' + rc1.CONSTRAINT_NAME + '];'
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc1
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc2 ON tc2.CONSTRAINT_NAME =rc1.CONSTRAINT_NAME

OPEN @Cursor FETCH NEXT FROM @Cursor INTO @Sql

WHILE (@@FETCH_STATUS = 0)
BEGIN
Exec sp_executesql @Sql
FETCH NEXT FROM @Cursor INTO @Sql
END

CLOSE @Cursor DEALLOCATE @Cursor
GO

EXEC sp_MSforeachtable 'DROP TABLE ?'
GO

Możesz znaleźć post tutaj . To jest post Grokera.


Dlaczego tak się dzieje, gdy próbujesz zawinąć cały blok kodu w instrukcję IF za pomocą bloku BEGIN-END, na który narzeka na kursor?
Adam

11
Dostaję błądCould not find stored procedure 'sp_MSForEachTable'.
Korayem,

2
Ta odpowiedź nie działa, jeśli masz tabele (z ograniczeniami) w innym schemacie niż dbo. Jeśli masz niestandardowe schematy, musisz zmienić na sql:SELECT DISTINCT sql = 'ALTER TABLE [' + tc2.CONSTRAINT_SCHEMA + '].[' + tc2.TABLE_NAME + '] DROP [' + rc1.CONSTRAINT_NAME + ']'
Asbjørn Ulsberg

5
sp_MSforeachtablenie jest dostępny na platformie Azure. Zamiast tego użyj odpowiedzi @ CountZero.
Ogglas,

3
Znaleziono to odniesienie w Internecie (nie moje): Kopia procedury przechowywanej sp_MSforeachtable dla platformy Azure, używa sp_MSforeach_worker: gist.github.com/metaskills/893599 .
João Vieira,

331

Możesz także usunąć wszystkie tabele z bazy danych, używając tylko narzędzi MSSMS UI (bez użycia skryptu SQL). Czasami ten sposób może być wygodniejszy (zwłaszcza jeśli jest wykonywany okazjonalnie)

Robię to krok po kroku w następujący sposób:

  1. Wybierz „Tabele” w drzewie bazy danych (Object Explorer)
  2. Naciśnij klawisz F7, aby otworzyć widok szczegółów Eksploratora obiektów
  3. W tym widoku wybierz tabele, które należy usunąć (w tym przypadku wszystkie)
  4. Naciskaj klawisz Delete, aż wszystkie tabele zostaną usunięte (powtórzysz tyle razy, ile błędów wynika z kluczowych ograniczeń / zależności)

5
Nie mogłem uzyskać sp_msforeachtachtable do pracy na bazie danych SQL Azure SQL. Zgaduję, że nie uwzględniają go na platformie Azure. To rozwiązanie działa świetnie i jest idealne, gdy kpi się z niego lub wprowadza wiele zmian (od nowa) w przypadku migracji EF.
trevorc

Nie uwzględnia FK / zamówienia.
Josh

1
jest to przydatna metoda, jeśli potrzebujesz to zrobić tu i tam, skrypt może być lepszy, jeśli robisz to często.
Dylan Hayes,

2
@Josh kolejność nie jest brana pod uwagę, ale jeśli będziesz nadal naciskać Ok w oknie dialogowym Usuń, będzie nadal usuwać wszystkie tabele, które może, co ostatecznie usunie wszystkie tabele.
clayRay

Idealne rozwiązanie przy użyciu Projektanta
Zaheer

49

W SSMS:

  • Kliknij bazę danych prawym przyciskiem myszy
  • Idź do „Zadań”
  • Kliknij „Generuj skrypty”
  • W sekcji „Wybierz obiekty” wybierz „Skryptuj całą bazę danych i wszystkie obiekty bazy danych”
  • W sekcji „Ustaw opcje skryptów” kliknij przycisk „Zaawansowane”
  • Na „Script DROP and CREATE” zmień „Script CREATE” na „Script DROP” i naciśnij OK
  • Następnie zapisz w pliku, schowku lub nowym oknie zapytania.
  • Uruchom skrypt.

Teraz wszystko spadnie, w tym baza danych. Pamiętaj, aby usunąć kod elementów, których nie chcesz upuszczać. Alternatywnie, w sekcji „Wybierz obiekty” zamiast wybierać skrypt do całej bazy danych, wystarczy wybrać elementy, które chcesz usunąć.


3
Jeśli nie chcesz upuścić bazy danych (czego w moim przypadku nie robię, to musiałbym zadzwonić do pomocy technicznej, aby ją utworzyć), użyj „Wybierz określone obiekty bazy danych” i wybierz wszystkie typy obiektów, które chcesz upuścić.
d219

3
To jest najłatwiejszy sposób.
Asiri Dissanayaka

2
To na pewno powinna być odpowiedź!
Zac Taylor,

34

deletesłuży do usuwania wierszy z tabeli. Zamiast tego powinieneś użyć drop table.

EXEC sp_msforeachtable 'drop table [?]'

Wydawało się, że to rozwiązało problem braku błędów, ale teraz napotykam błędy ograniczeń. Czy składnia mojej NOCHECK CONSTRAINTlinii jest nieprawidłowa?
dixuji,

1
Użyłem tego na szybkich 2 stołach z FK's i zadziałało. EXEC sp_msforeachtable 'ALTER TABLE? NOCHECK
CONSTRAINT

12
Działa to tylko po usunięciu nawiasów kwadratowych: EXEC sp_msforeachtable 'drop table?'.
LPains

W SQL Server potrzebujesz nawiasów, jeśli nazwa tabeli zawiera nieparzyste znaki lub spacje. Nie znałem wersji SQL Server, która nie obsługiwała nawiasów wokół nazw obiektów.
DaveShaw

W SQL Server 2012 (11.x) musiałem również usunąć nawiasy kwadratowe.
Frieder

30

Akceptowana odpowiedź nie obsługuje platformy Azure. Korzysta z nieudokumentowanej procedury składowanej „sp_MSforeachtable”. Jeśli pojawi się błąd „lazur nie może znaleźć procedury przechowywanej” sp_msforeachtableable podczas działania lub po prostu chcesz uniknąć polegania na nieudokumentowanych funkcjach (które można usunąć lub zmienić ich funkcjonalność w dowolnym momencie), spróbuj wykonać poniższe czynności.

Ta wersja ignoruje tabelę historii migracji struktury encji „__MigrationHistory” i „database_firewall_rules”, który jest tabelą platformy Azure, której nie będziesz mieć uprawnień do usunięcia.

Lekko przetestowany na platformie Azure. Sprawdź, czy to nie ma niepożądanego wpływu na środowisko.

DECLARE @sql NVARCHAR(2000)

WHILE(EXISTS(SELECT 1 from INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE='FOREIGN KEY'))
BEGIN
    SELECT TOP 1 @sql=('ALTER TABLE ' + TABLE_SCHEMA + '.[' + TABLE_NAME + '] DROP CONSTRAINT [' + CONSTRAINT_NAME + ']')
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
    WHERE CONSTRAINT_TYPE = 'FOREIGN KEY'
    EXEC(@sql)
    PRINT @sql
END

WHILE(EXISTS(SELECT * from INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME != '__MigrationHistory' AND TABLE_NAME != 'database_firewall_rules'))
BEGIN
    SELECT TOP 1 @sql=('DROP TABLE ' + TABLE_SCHEMA + '.[' + TABLE_NAME + ']')
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_NAME != '__MigrationHistory' AND TABLE_NAME != 'database_firewall_rules'
    EXEC(@sql)
    PRINT @sql
END

Pochodzą z:

https://edspencer.me.uk/2013/02/25/drop-all-tables-in-a-sql-server-database-azure-friendly/

http://www.sqlservercentral.com/blogs/sqlservertips/2011/10/11/remove-all-foreign-keys/


3
Pracował jako magia na lazurach. Znacznie szybciej niż usuwanie za pomocą interfejsu VS
Simeona Grigorowicza

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

1
Działa w SQL Azure :) sp_MSforeachtable tam nie działa
Pavel Biryukov

Uwaga: to się nie powiedzie, jeśli masz schematy inne niż dbo (np. Jeśli używasz Hangfire)
Liam Dawson

21

Masz prawie rację, zamiast tego użyj:

EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all'
EXEC sp_msforeachtable 'DROP TABLE ?'

ale druga linia może wymagać wykonania więcej niż raz, aż przestanie się pojawiać błąd:

Could not drop object 'dbo.table' because it is referenced by a FOREIGN KEY constraint.

Wiadomość:

Command(s) completed successfully.

oznacza, że ​​wszystkie tabele zostały pomyślnie usunięte.


17

Krótkie i słodkie:

USE YOUR_DATABASE_NAME
-- Disable all referential integrity constraints
EXEC sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
GO

-- Drop all PKs and FKs
declare @sql nvarchar(max)
SELECT @sql = STUFF((SELECT '; ' + 'ALTER TABLE ' + Table_Name  +'  drop constraint ' + Constraint_Name  from Information_Schema.CONSTRAINT_TABLE_USAGE ORDER BY Constraint_Name FOR XML PATH('')),1,1,'')
EXECUTE (@sql)
GO

-- Drop all tables
EXEC sp_MSforeachtable 'DROP TABLE ?'
GO

2
Musiałem zastąpić sp_msforeachtableprzez sp_MSforeachtablei bardzo polecam, aby dodać use yourdatabasejako pierwszej linii. Działa jak urok.
Simon Sobisch

Krótkie i słodkie! Dziękuję
sapatelbaps

7

Postem jest:

  1. Nowe diagramy bazy danych
  2. Dodaj cały stół
  3. Ctrl + A, aby zaznaczyć wszystko
  4. Kliknij prawym przyciskiem myszy „Usuń z bazy danych”
  5. Ctrl + S, aby zapisać
  6. Cieszyć się

Jest to zdecydowanie najszybszy, mniej podatny na błędy sposób na zrobienie tego. To powinna być zaakceptowana odpowiedź.
DARKGuy

6

Wydaje się, że polecenie powinno być bez kwadratowego koca

EXEC sp_msforeachtable 'drop table ?'

6

Dla mnie najprostszy sposób:

--First delete all constraints

DECLARE @sql NVARCHAR(MAX);
SET @sql = N'';

SELECT @sql = @sql + N'
ALTER TABLE ' + QUOTENAME(s.name) + N'.'
+ QUOTENAME(t.name) + N' DROP CONSTRAINT '
+ QUOTENAME(c.name) + ';'
FROM sys.objects AS c
INNER JOIN sys.tables AS t
ON c.parent_object_id = t.[object_id]
INNER JOIN sys.schemas AS s 
ON t.[schema_id] = s.[schema_id]
WHERE c.[type] IN ('D','C','F','PK','UQ')
ORDER BY c.[type];

EXEC sys.sp_executesql @sql;

-- Then drop all tables

exec sp_MSforeachtable 'DROP TABLE ?'

2

Spot na !!

Możesz użyć poniższego zapytania, aby usunąć wszystkie tabele z bazy danych

EXEC sp_MSforeachtable @ command1 = "DROP TABLE?"

Miłego kodowania!


1

Co powiesz na usunięcie całej bazy danych, a następnie utworzenie jej ponownie? To działa dla mnie.

DROP DATABASE mydb;
CREATE DATABASE mydb;

Nie musisz wysadzać plików mdf i ldf pomiędzy nimi?
ruffin

Używam usługodawcy hostingowego i nie jestem tego pewien. Myślę, że te pliki zostaną automatycznie usunięte po upuszczeniu bazy danych, chyba że jesteś offline.
Chong Lip Phang

2
To naprawdę zależy od okoliczności. W moim przypadku nie mam procedur przechowywanych i ta metoda działa dla mnie dobrze. Nie zamierzam pisać skomplikowanych kodów obejmujących dziesiątki linii, gdy wystarczą dwie linie. Nie sądzę, aby moja odpowiedź uzasadniała głosowanie negatywne, ponieważ proponuję konkretną grupę użytkowników.
Chong Lip Phang

0

Wiem, że jest to stary post, ale wypróbowałem wszystkie odpowiedzi tutaj na wielu bazach danych i okazało się, że wszystkie one działają czasami, ale nie przez cały czas, dla różnych (mogę tylko założyć) dziwactw SQL Servera.

W końcu to wymyśliłem. Przetestowałem to wszędzie (ogólnie rzecz biorąc) mogę i działa (bez żadnych ukrytych procedur przechowywania).

Uwaga, głównie na SQL Server 2014. (ale większość innych wersji, które wypróbowałem, również działa dobrze).

Próbowałem podczas pętli i zer, itp., Kursorów i różnych innych form, ale zawsze wydają się zawieść w niektórych bazach danych, ale nie w innych bez wyraźnego powodu.

Wyliczanie i używanie tego do iteracji zawsze wydaje się działać na wszystko, co przetestowałem.

USE [****YOUR_DATABASE****]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- Drop all referential integrity constraints --
-- Drop all Primary Key constraints.          --

DECLARE @sql  NVARCHAR(296)
DECLARE @table_name VARCHAR(128)

DECLARE @constraint_name VARCHAR(128)
SET @constraint_name = ''

DECLARE @row_number INT

SELECT @row_number = Count(*) FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc1
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc2 ON tc2.CONSTRAINT_NAME = rc1.CONSTRAINT_NAME

WHILE @row_number > 0
BEGIN
    BEGIN
        SELECT TOP 1 @table_name = tc2.TABLE_NAME, @constraint_name = rc1.CONSTRAINT_NAME FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc1
        LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc2 ON tc2.CONSTRAINT_NAME = rc1.CONSTRAINT_NAME
        AND rc1.CONSTRAINT_NAME > @constraint_name
        ORDER BY rc1.CONSTRAINT_NAME
        SELECT @sql = 'ALTER TABLE [dbo].[' + RTRIM(@table_name) +'] DROP CONSTRAINT [' + RTRIM(@constraint_name)+']'
        EXEC (@sql)
        PRINT 'Dropped Constraint: ' + @constraint_name + ' on ' + @table_name
        SET @row_number = @row_number - 1
    END
END
GO

-- Drop all tables --

DECLARE @sql  NVARCHAR(156)
DECLARE @name VARCHAR(128)
SET @name = ''

DECLARE @row_number INT

SELECT @row_number = Count(*) FROM sysobjects WHERE [type] = 'U' AND category = 0

WHILE @row_number > 0
BEGIN
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 AND [name] > @name ORDER BY [name])
    SELECT @sql = 'DROP TABLE [dbo].[' + RTRIM(@name) +']'
    EXEC (@sql)
    PRINT 'Dropped Table: ' + @name
    SET @row_number = @row_number - 1
END
GO

0

W przypadku tabel czasowych jest to nieco bardziej skomplikowane, ponieważ mogą istnieć pewne klucze obce, a także wyjątek:

Drop table operation failed on table XXX because it is not a supported operation on system-versioned temporal tables

Możesz użyć:

-- Disable constraints (foreign keys)
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
GO

-- Disable system versioning (temporial tables)
EXEC sp_MSForEachTable '
 IF OBJECTPROPERTY(object_id(''?''), ''TableTemporalType'') = 2
  ALTER TABLE ? SET (SYSTEM_VERSIONING = OFF)
'
GO

-- Removing tables
EXEC sp_MSForEachTable 'DROP TABLE ?'
GO
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.