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

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?

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.

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)

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ąć.

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

EXEC sp_msforeachtable 'drop table [?]'

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/

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

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.

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

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ę

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

EXEC sp_msforeachtable 'drop table ?'

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 ?'

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!

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;

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

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