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=@counter–1
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=@counter–1
end;
