How to drop tables database wide using T-SQL

There are some cases that we need to drop all tables database wide. For example when we have a test database and we need to delete all tables from the database and recreate some other tables for testing we need to drop each table manually using the GUI. Assume we have 50 tables in the database, so, we need to drop the tables one-by-one and repeat the process 50 times. Actually it is getting harder when there are some relationships between the tables!

So, what should we do now? Okay, we need to drop all dependencies first and the drop all 50 tables! It’s frustrating, isn’t it?

Why shouldn’t we do that programmatically using T-SQL? Copy and paste the following code in the SSMS (SQL Server Management Studio) and press F5 easily. All done. Easy!

declare @FK table (No int, tbl nvarchar(max), fk nvarchar(max))

declare @counter int

declare @sql nvarchar(max)

declare @table nvarchar(max)

declare @Constraint nvarchar(max)

insert into @FK (No, tbl , fk)

select ROW_NUMBER() OVER (ORDER BY f.name),t.name, f.name from sys.foreign_keys f inner join sys.tables t on f.parent_object_id=t.object_id

set @counter= (select count(*) from @FK)

while @counter >0

begin

set @table = (select tbl from @FK where No=@counter)

set @Constraint = (select fk from @FK where No=@counter)

set @sql = ‘ALTER TABLE [dbo].[‘+@table+‘]  DROP  CONSTRAINT [‘+@Constraint+‘]’

exec SP_EXECUTESQL @sql

set @counter=@counter1

end

delete from @FK

insert into @FK (No, tbl, fk)

select ROW_NUMBER() OVER (ORDER BY t.name),t.name, NA=null from sys.tables t

set @counter= (select count(*) from @FK)

while @counter >0

begin

set @table = (select tbl from @FK where No=@counter)

set @sql = ‘DROP TABLE [dbo].[‘+@table+‘]’

exec SP_EXECUTESQL @sql

set @counter=@counter1

end;

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.