Hoe verwijder je alle tabellen in een SQL Server-database?

Ik probeer een script te schrijven dat een SQL Server-database volledig leegmaakt. Dit is wat ik tot nu toe heb:

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

Als ik het in Management Studio uitvoer, krijg ik:

Opdracht(en) succesvol afgerond.

maar als ik de tabellijst ververs, zijn ze er allemaal nog steeds. Wat doe ik verkeerd?


Antwoord 1, autoriteit 100%

Het werkt bij mij ook niet als er meerdere tabellen met refererende sleutels zijn.
Ik heb die code gevonden die werkt en alles doet wat je probeert (verwijder alle tabellen uit je database):

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

Je kunt het bericht hier. Het is de post van Groker.


Antwoord 2, autoriteit 92%

U kunt ook alle tabellen uit de database verwijderen met alleen MSSMS UI-tools (zonder SQL-script te gebruiken). Soms kan deze manier comfortabeler zijn (vooral als het af en toe wordt uitgevoerd)

Ik doe dit stap voor stap als volgt:

  1. Selecteer ‘Tabellen’ in de databasestructuur (Objectverkenner)
  2. Druk op F7 om de detailweergave van Objectverkenner te openen
  3. Selecteer in deze weergave tabellen die moeten worden verwijderd (in dit geval allemaal)
  4. Blijf op Delete drukken totdat alle tabellen zijn verwijderd (u herhaalt het zo vaak als het aantal fouten vanwege belangrijke beperkingen/afhankelijkheden)

Antwoord 3, autoriteit 22%

In SSMS:

  • Klik met de rechtermuisknop op de database
  • Ga naar “Taken”
  • Klik op “Scripts genereren”
  • Selecteer in de sectie “Objecten kiezen” de optie “Volledige database en alle database-objecten scripten”
  • Klik in het gedeelte “Scriptingopties instellen” op de knop “Geavanceerd”
  • Schakel op “Script DROP en CREATE” “Script CREATE” naar “Script DROP” en druk op OK
  • Sla vervolgens op in bestand, klembord of nieuw zoekvenster.
  • Script uitvoeren.

Dit zal alles laten vallen, inclusief de database. Zorg ervoor dat u de code verwijdert voor de items die u niet wilt laten vallen. U kunt ook in de sectie “Objecten kiezen”, in plaats van te selecteren om de volledige database te scripten, gewoon de items selecteren die u wilt verwijderen.


Antwoord 4, autoriteit 13%

Het geaccepteerde antwoord biedt geen ondersteuning voor Azure. Het gebruikt een ongedocumenteerde opgeslagen procedure “sp_MSforeachtable”. Als u tijdens het uitvoeren de foutmelding ‘Azure kon de opgeslagen procedure ‘sp_msforeachtable’ niet vinden, of u wilt gewoon voorkomen dat u vertrouwt op niet-gedocumenteerde functies (die kunnen worden verwijderd of waarvan de functionaliteit op elk moment kan worden gewijzigd), probeer dan het onderstaande.

Deze versie negeert de migratiegeschiedenistabel van het entiteitsframework “__MigrationHistory” en de “database_firewall_rules”, een Azure-tabel die u niet mag verwijderen.

Licht getest op Azure. Wel even aanvinken dat dit geen ongewenste effecten heeft op uw omgeving.

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

Genomen van:

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/


Antwoord 5, autoriteit 12%

deletewordt gebruikt voor het verwijderen van rijen uit een tabel. Gebruik in plaats daarvan drop table.

EXEC sp_msforeachtable 'drop table [?]'

Antwoord 6, autoriteit 8%

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

Antwoord 7, autoriteit 7%

Je hebt bijna gelijk, gebruik in plaats daarvan:

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

maar de tweede regel moet u mogelijk meer dan één keer uitvoeren totdat u geen foutmelding meer krijgt:

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

Bericht:

Command(s) completed successfully.

betekent dat alle tabellen met succes zijn verwijderd.


Antwoord 8, autoriteit 6%

Kort en krachtig:

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

Antwoord 9, autoriteit 2%

Het lijkt erop dat de opdracht zonder de vierkante deken zou moeten zijn

EXEC sp_msforeachtable 'drop table ?'

Antwoord 10, autoriteit 2%

De snelste manier is:

  1. Nieuwe databasediagrammen
  2. Alle tabellen toevoegen
  3. Ctrl + A om alles te selecteren
  4. Klik met de rechtermuisknop op “Verwijderen uit database”
  5. Ctrl + S om op te slaan
  6. Geniet

Antwoord 11, autoriteit 2%

Voor mij de gemakkelijkste manier:

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

Antwoord 12, autoriteit 2%

Plek op!!

U kunt onderstaande query gebruiken om alle tabellen uit de database te verwijderen

EXEC sp_MSforeachtable @command1 = “DROP TABLE ?”

Veel plezier met coderen!


Antwoord 13

Hoe zit het met het verwijderen van de hele database en het opnieuw maken? Dit werkt voor mij.

DROP DATABASE mydb;
CREATE DATABASE mydb;

Antwoord 14

Ik weet nu dat dit een oud bericht is, maar ik heb alle antwoorden hier op een groot aantal databases geprobeerd en ik heb ontdekt dat ze allemaal soms werken, maar niet altijd voor verschillende (ik kan alleen maar aannemen) eigenaardigheden van SQL Server.

Uiteindelijk heb ik dit bedacht. Ik heb dit overal (over het algemeen) getest waar ik kan en het werkt (zonder verborgen winkelprocedures).

Voornamelijk op SQL Server 2014. (maar de meeste andere versies die ik heb geprobeerd, lijken ook goed te werken).

Ik heb while-loops en nulls enz enz., cursors en verschillende andere vormen geprobeerd, maar ze lijken altijd te falen in sommige databases, maar niet in andere zonder duidelijke reden.

Een telling krijgen en die gebruiken om te herhalen, lijkt altijd te werken op alles wat ik heb getest.

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

Antwoord 15

Voor Tijdelijke tabellenis het een beetje ingewikkelder vanwege het feit dat er enkele buitenlandse sleutels en ook uitzonderingen kunnen zijn:

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

Wat u kunt gebruiken is:

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

Other episodes