Delete all the user tables in SQL Server 2012

At times we need to delete all the user tables in a SQL Server 2012 database. Why do we need to do that? This may happen if:

  • The data is used for analysis or reporting
  • You are reloading tables every night via a batch
  • There could be application development or testing
  • We have a need to delete objects for a certain schema

 

So this means we have to keep the database intact, just need to drop the tables. There may be other objects like stored procedures, views, user logins and even SQL jobs associated with the database. How do we do that, I mean keep the database but zap all the user tables. Well there are really two options. One is to do it the “old fashioned” way, delete a table one at a time. The other is more elegant and uses TSQL to delete all the user tables in SQL Server 2012. Since we also belong to the Lazy DBA Association, we are going to use TSQL to achieve the end result. We will be doing the following:

  1. Create a temp table
  2. Insert the list of tables into this temp table
  3. Loop through list of tables and zap them one at a time

Note; Make sure the Referential Integrity is disabled, otherwise this will not work

 

use Northwind_test

–declaration of variables

declare
@init_count int,
@rec_count int,
@total_count int,
@table_name varchar(1000),
@sql_stmt varchar(1000)

–create temp table #tmp_tables, more info in another script

create table #tmp_tables
(id int identity(1,1),
tablename varchar(50)
)

–insert into temp table #tmp_tables

insert into #tmp_tables (tablename)
SELECT TABLE_SCHEMA + ‘.’ + TABLE_NAME
FROM information_schema.tables
where TABLE_TYPE=‘base table’

set @init_count=1
select @total_count=count(*) from #tmp_tables
set @rec_count=@init_count

print @init_count
print @rec_count
print @total_count

–while loop to go through the tables

while @rec_count < @total_count

begin
select @table_name= tablename from #tmp_tables where id=@rec_count
select @sql_stmt=‘drop table ‘ + @table_name
–print @table_name
print @sql_stmt
exec (@sql_stmt)

set @rec_count=@rec_count + 1
end

drop table #tmp_tables
/* Sample output

drop table dbo.Employees
drop table dbo.Categories
drop table dbo.Customers
drop table dbo.Shippers
drop table dbo.Suppliers
drop table dbo.Orders
drop table dbo.Products
drop table dbo.Order Details
drop table dbo.CustomerCustomerDemo
drop table dbo.CustomerDemographics
drop table dbo.Region
drop table dbo.Territories
drop table dbo.EmployeeTerritories
drop table dbo.Customers_All

*/

 

Follow us on: