Delete all tables in SQL Server database?

220 pts.
Tags:
DELETE statement
SQL Database
SQL Server
SQL Server database
SQL tables
How to delete all tables of a database in SQL Server?

Software/Hardware used:
Delete all tables?
ASKED: November 15, 2010  8:28 AM
UPDATED: November 16, 2010  4:15 AM

Answer Wiki

Thanks. We'll let you know when a new response is added.

Sql Server has an option of navigating to all tables using stored procedure sp_MSforeachtable

For e.g the following command will drop all the tables in the database:

exec sp_MSforeachtable 'DROP TABLE ?'

The below command will clear all the tables:

exec sp_MSforeachtable 'DELETE FROM ?'

So by using the procedure sp_MSforeachtable you can navigate to all the tables and do your task like cleaning table, dropping table etc.

If the database is no longer use, so far you may use this command also:
drop database <yourdatabasename>

Or you may use GUI feature to drop table from one database one by one!

Discuss This Question: 4  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Robert2010
    This is extremely quick and dirty - [sql]select name into #tables from sys.objects where type = ‘U’ while (select count(1) from #tables) > 0 begin declare @sql varchar(max) declare @tbl varchar(255) select top 1 @tbl = name from #tables set @sql = ‘drop table ‘ + @tbl exec(@sql) delete from #tables where name = @tbl end drop table #tables;[/sql] But this fails when there are foriegn key references in the tables. I'm 70% confident that this will work correctly.
    465 pointsBadges:
    report
  • Prcvjr
    [...] 2. Mr. Denny, Robert2010 and Rechil have given some great options for deleting all tables in a SQL Server database. [...]
    0 pointsBadges:
    report
  • Prcvjr
    [...] Wondering how to delete all tables in SQL Server database? Rechil, Mr. Denny and Robert2010 give some [...]
    0 pointsBadges:
    report
  • karimsoft
    try this code
    	declare @tmp TABLE ([name] [sysname] NOT NULL)
    
    	insert into @tmp 
    	select name 
    	from sys.objects where type = 'U'
    
    	declare @sql varchar(max)=''
    
    	select @sql =@sql + 'drop table ' + name  + '; ' from @tmp
    	exec(@sql)



    10 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following