How to drop all tables in a SQL Server database

Question

I m trying to write a script that will completely empty a SQL Server database  This is what I have so far   USE  dbname  GO EXEC sp msforeachtable  ALTER TABLE   NOCHECK CONSTRAINT all  EXEC sp msforeachtable  DELETE      When I run it in the Management Studio  I get      Command s  completed successfully    but when I refresh the table list  they are all still there  What am I doing wrong

User · Answer

delete is used for deleting rows from a table  You should use drop table instead   EXEC sp msforeachtable  drop table

User · Answer

It doesn t work for me either when there are multiple foreign key tables  I found that code that works and does everything you try  delete all tables from your 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   You can find the post here  It is the post by Groker

User · Answer

You are almost right  use instead   EXEC sp msforeachtable  ALTER TABLE   NOCHECK CONSTRAINT all  EXEC sp msforeachtable  DROP TABLE      but second line you might need to execute more then once until you stop getting error   Could not drop object  dbo table  because it is referenced by a FOREIGN KEY constraint    Message   Command s  completed successfully    means that all table were successfully deleted

User · Answer

For me  the easiest way   --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

User · Answer

In SSMS     Right click the database Go to  Tasks  Click  Generate Scripts  In the  Choose Objects  section  select  Script entire database and all database objects  In the  Set Scripting Options  section  click the  Advanced  button On  Script DROP and CREATE  switch  Script CREATE  to  Script DROP  and press OK Then  either save to file  clipboard  or new query window  Run script    Now  this will drop everything  including the database  Make sure to remove the code for the items you don t want dropped  Alternatively  in the  Choose Objects  section  instead of selecting to script entire database just select the items you want to remove

User · Answer

For Temporal Tables it is a bit more complicated due to the fact there may be some foreign keys and also exception   Drop table operation failed on table XXX because it is not a supported operation on system-versioned temporal tables  What you can use 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

User · Answer

You can also delete all tables from database using only MSSMS UI tools  without using SQL script   Sometimes this way can be more comfortable  especially if it is performed occasionally   I do this step by step as follows    Select  Tables  on the database tree  Object Explorer  Press F7 to open Object Explorer Details view In this view select tables which have to be deleted  in this case all of them  Keep pressing Delete until all tables have been deleted  you repeat it as many times as amount of errors due to key constraints dependencies

User · Answer

Seems the command should be without the square blanket  EXEC sp msforeachtable  drop table

User · Answer

The accepted answer doesn t support Azure  It uses an undocumented stored procedure  sp MSforeachtable   If you get an  azure could not find stored procedure  sp msforeachtable  error when running or simply want to avoid relying on undocumented features  which can be removed or have their functionality changed at any point  then try the below    This version ignores the entity framework migration history table    MigrationHistory  and the  database firewall rules  which is an Azure table you will not have permission to delete    Lightly tested on Azure  Do check to make this this has no undesired effects on your environment      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   Taken from   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

User · Answer

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  lt  gt   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   gt   name ORDER BY  name   END GO

User · Answer

How about dropping the entire database and then creating it again  This works for me   DROP DATABASE mydb  CREATE DATABASE mydb

User · Answer

I know this is an old post now but I have tried all the answers on here on a multitude of databases and I have found they all work sometimes but not all of the time for various  I can only assume  quirks of SQL Server   Eventually I came up with this  I have tested this everywhere  generally speaking  I can and it works  without any hidden store procedures     For note mostly on SQL Server 2014   but most of the other versions I tried it also seems to worked fine    I have tried while loops and nulls etc etc  cursors and various other forms but they always seem to fail on some databases but not others for no obvious reason   Getting a count and using that to iterate always seems to work on everything Ive tested   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  gt  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  gt   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  gt  0 BEGIN     SELECT  name    SELECT TOP 1  name  FROM sysobjects WHERE  type     U  AND category   0 AND  name   gt   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

User · Answer

The fasted way is    New Database Diagrams Add all table Ctrl   A to select all Right Click  Remove from Database  Ctrl   S to save Enjoy

User · Answer

Spot on    You can use below query to remove all the tables from database  EXEC sp MSforeachtable  command1    DROP TABLE     Happy coding

User · Answer

Short and sweet   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

[sql-server] How to drop all tables in a SQL Server database?

Examples related to sql-server

Examples related to sql-server-2008

Examples related to ssms

Examples related to sp-msforeachtable