How to drop all tables in a database?

How to drop all tables in a database?

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?

Answer by DaveShaw for How to drop all tables in a database?

delete is used for deleting rows from a table. You should use drop table instead.

EXEC sp_msforeachtable 'drop table [?]'  

Answer by Gabriel GM for How to drop all tables in a database?

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

Answer by Harpal for How to drop all tables in a database?

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

Answer by Micha Powaga for How to drop all tables in a database?

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.

Answer by Bronek for How to drop all tables in a database?

You can also delete all tables from database using only MSSMS tools (without using sql). Sometimes this way can be more comfortable (especially if it is performed occasionally)

I do this step by step as follows:

1. Select 'Tables' on the database tree (Object Explorer)
2. Press F7 to open Object Explorer Details view
3. In this view select tables which have to be deleted (in this case all of them)
4. Press Delete until all tables are deleted (you repeat it as many times as amount of errors due to key constraints/dependencies)

Answer by Serj Sagan for How to drop all tables in a database?

If none of the solutions on this page are not working, perhaps forgot to:

USE [DatabaseName]  GO  

Answer by Chong Lip Phang for How to drop all tables in a database?

How about dropping the entire database and then creating it again? This works for me.

DROP DATABASE mydb;  CREATE DATABASE mydb;  

Fatal error: Call to a member function getElementsByTagName() on a non-object in D:\XAMPP INSTALLASTION\xampp\htdocs\endunpratama9i\www-stackoverflow-info-proses.php on line 72