Home > IT Answers > Database > Delete all tables in SQL Server database?
Prcvjr
220 pts.
 Delete all tables in SQL Server database?
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
RELATED QUESTIONS
Answer Wiki:
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!
Last Wiki Answer Submitted:  April 19, 2013  4:54 pm  by  Michael Tidmarsh   14,000 pts.
All Answer Wiki Contributors:  Michael Tidmarsh   14,000 pts. , Denny Cherry   64,550 pts. , Subhendu Sen   22,125 pts.
To see all answers submitted to the Answer Wiki: View Answer History.


RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

 

This is extremely quick and dirty -

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;

But this fails when there are foriegn key references in the tables.

I’m 70% confident that this will work correctly.

Robert2010
 465 pts.