How to drop all tables from a database with one SQL query

Question

I don t want to type all tables  name to drop all of them  Is it possible with one query

User · Answer

The simplest way is to drop the whole database and create it once again   drop database db name create database db name   That s all

User · Answer

I Know this question is very old but every Time i need this code    by the way if you have tables and views and Functions and PROCEDURES you can delete it all by this Script    so why i post this Script    because if u delete all tables you will need to delete all views and if you have Functions and PROCEDURES  you need to delete it too i Hope it will help someone  DECLARE  sql NVARCHAR max       SELECT  sql      Drop table     QUOTENAME TABLE SCHEMA         QUOTENAME TABLE NAME          FROM   INFORMATION SCHEMA TABLES WHERE  TABLE TYPE    BASE TABLE   Exec Sp executesql  sql    DECLARE  sql VARCHAR MAX              crlf VARCHAR 2    CHAR 13    CHAR 10      SELECT  sql    sql    DROP VIEW     QUOTENAME SCHEMA NAME schema id             QUOTENAME v name          crlf  FROM   sys views v   PRINT  sql   EXEC  sql     declare  procName varchar 500   declare cur cursor    for select  name  from sys objects where type    p   open cur  fetch next from cur into  procName  while   fetch status   0  begin   exec  drop procedure       procName        fetch next from cur into  procName  end   close cur   deallocate cur    Declare  sql NVARCHAR MAX    N         SELECT  sql    sql   N  DROP FUNCTION                     QUOTENAME SCHEMA NAME schema id                     N      QUOTENAME name      FROM sys objects   WHERE type desc LIKE   FUNCTION        Exec sp executesql  sql   GO

User · Answer

I d just make a small change to  NoDisplayName s answer and use QUOTENAME   on the TABLE NAME column and also include the TABLE SCHEMA column encase the tables aren t in the dbo schema   DECLARE  sql nvarchar max         SELECT  sql     DROP TABLE     QUOTENAME  TABLE SCHEMA           QUOTENAME  TABLE NAME         FROM  INFORMATION SCHEMA   TABLES  WHERE  TABLE TYPE     BASE TABLE    EXEC SP EXECUTESQL  sql    Or using sys schema views  as per  swasheck s comment    DECLARE  sql nvarchar max         SELECT  sql     DROP TABLE     QUOTENAME  S   name           QUOTENAME  T   name         FROM  sys   tables  AS  T  INNER JOIN  sys   schemas  AS  S  ON   T   schema id     S   schema id   WHERE  T   type     U  AND  T   is ms shipped    0   EXEC SP EXECUTESQL  sql

User · Answer

Use the following script to drop all constraints   DECLARE  sql NVARCHAR max      SELECT  sql      ALTER TABLE     QUOTENAME TABLE SCHEMA         QUOTENAME TABLE NAME         NOCHECK CONSTRAINT all    FROM   INFORMATION SCHEMA TABLES WHERE  TABLE TYPE    BASE TABLE   Exec Sp executesql  sql   Then run the following to drop all tables   select  sql      SELECT  sql      Drop table     QUOTENAME TABLE SCHEMA         QUOTENAME TABLE NAME         FROM   INFORMATION SCHEMA TABLES WHERE  TABLE TYPE    BASE TABLE   Exec Sp executesql  sql   This worked for me in Azure SQL Database where  sp msforeachtable  was not available

User · Answer

If you don t want to type  you can create the statements with this   USE Databasename  SELECT   DROP TABLE      name        FROM    sys tables   Then copy and paste into a new SSMS window to run it

User · Answer

Use the INFORMATION SCHEMA TABLES view to get the list of tables  Generate Drop scripts in the select statement and drop it using Dynamic SQL   DECLARE  sql NVARCHAR max      SELECT  sql      Drop table     QUOTENAME TABLE SCHEMA         QUOTENAME TABLE NAME         FROM   INFORMATION SCHEMA TABLES WHERE  TABLE TYPE    BASE TABLE   Exec Sp executesql  sql   Sys Tables Version  DECLARE  sql NVARCHAR max      SELECT  sql      Drop table     QUOTENAME s NAME          QUOTENAME t NAME         FROM   sys tables t        JOIN sys schemas s          ON t  schema id    s  schema id  WHERE  t type    U   Exec sp executesql  sql   Note  If you have any foreign Keys defined between tables then first run the below query to disable all foreign keys present in your database   EXEC sp msforeachtable  ALTER TABLE   NOCHECK CONSTRAINT all    For more information  check here

User · Answer

If anybody else had a problem with best answer s solution  including disabling foreign keys   here is another solution from me   -- CLEAN DB USE  DB NAME      EXEC sp MSForEachTable  ALTER TABLE   NOCHECK CONSTRAINT ALL      EXEC sp MSForEachTable  DELETE FROM         DECLARE  Sql NVARCHAR 500  DECLARE  Cursor CURSOR     SET  Cursor   CURSOR FAST FORWARD FOR      SELECT DISTINCT sql    ALTER TABLE      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      EXEC sp MSForEachTable  ALTER TABLE   WITH CHECK CHECK CONSTRAINT ALL

User · Answer

You could also use the following script to drop everything  including the following    non-system stored procedures views functions foreign key constraints primary key constraints tables   https   michaelreichenbach de how-to-drop-everything-in-a-mssql-database      Drop all non-system stored procs    DECLARE  name VARCHAR 128  DECLARE  SQL VARCHAR 254   SELECT  name    SELECT TOP 1  name  FROM sysobjects WHERE  type     P  AND category   0 ORDER BY  name    WHILE  name is not null BEGIN     SELECT  SQL    DROP PROCEDURE  dbo       RTRIM  name           EXEC   SQL      PRINT  Dropped Procedure       name     SELECT  name    SELECT TOP 1  name  FROM sysobjects WHERE  type     P  AND category   0 AND  name   gt   name ORDER BY  name   END GO     Drop all views    DECLARE  name VARCHAR 128  DECLARE  SQL VARCHAR 254   SELECT  name    SELECT TOP 1  name  FROM sysobjects WHERE  type     V  AND category   0 ORDER BY  name    WHILE  name IS NOT NULL BEGIN     SELECT  SQL    DROP VIEW  dbo       RTRIM  name           EXEC   SQL      PRINT  Dropped View       name     SELECT  name    SELECT TOP 1  name  FROM sysobjects WHERE  type     V  AND category   0 AND  name   gt   name ORDER BY  name   END GO     Drop all functions    DECLARE  name VARCHAR 128  DECLARE  SQL VARCHAR 254   SELECT  name    SELECT TOP 1  name  FROM sysobjects WHERE  type  IN  N FN   N IF   N TF   N FS   N FT   AND category   0 ORDER BY  name    WHILE  name IS NOT NULL BEGIN     SELECT  SQL    DROP FUNCTION  dbo       RTRIM  name           EXEC   SQL      PRINT  Dropped Function       name     SELECT  name    SELECT TOP 1  name  FROM sysobjects WHERE  type  IN  N FN   N IF   N TF   N FS   N FT   AND category   0 AND  name   gt   name ORDER BY  name   END GO     Drop all Foreign 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    FOREIGN 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    FOREIGN 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 FK Constraint       constraint     on      name         SELECT  constraint    SELECT TOP 1 CONSTRAINT NAME FROM INFORMATION SCHEMA TABLE CONSTRAINTS WHERE constraint catalog DB NAME   AND CONSTRAINT TYPE    FOREIGN 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    FOREIGN KEY  ORDER BY TABLE NAME  END GO     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

For me I just do  DECLARE  cnt INT   0   WHILE  cnt  lt  10 --Change this if all tables are not dropped with one run BEGIN SET  cnt    cnt   1  EXEC sp MSforeachtable  command1    quot DROP TABLE   quot  END

User · Answer

If you want to use only one SQL query to delete all tables you can use this   EXEC sp MSforeachtable  command1    DROP TABLE      This is a hidden Stored Procedure in sql server  and will be executed for each table in the database you re connected   Note  You may need to execute the query a few times to delete all tables due to dependencies   Note2  To avoid the first note  before running the query  first check if there foreign keys relations to any table  If there are then just disable foreign key constraint by running the query bellow   EXEC sp msforeachtable  ALTER TABLE   NOCHECK CONSTRAINT all

User · Answer

Not quite 1 query  still quite short and sweet   -- 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

User · Answer

As a follow-up to Dave Gugg s answer  this would be the code someone would need to get all the DROP TABLE lines in MySQL   SELECT CONCAT  DROP TABLE    TABLE NAME       FROM INFORMATION SCHEMA tables WHERE TABLE SCHEMA    your database name

[sql] How to drop all tables from a database with one SQL query?

Examples related to sql

Examples related to sql-server