In my previous post called “How to drop tables database wide using T-SQL” I’ve explained that how we could delete all tables database wide in SQL Server. But, what if there are some other schemas other than “dbo” and some tables that are created in those schemas? If we just delete “[dbo].” from the code we will obviously face to the following error message: “Cannot find the object “TABLE_NAME” because it does not exist or you do not have permissions.” for those tables that are not under “dbo” schema. So, we need to retrieve all schemas and their related tables. This approach is really helpful when we are implementing an ETL process that is designed to delete the tables from particular schemas and recreate the tables and populate new data. This is a common way for staging to delete and recreate tables instead of updating existing tables as update is really costly.
So, let’s write some codes.
Note: The following code is written to delete the tables under a specific schema. If you want to use it to delete all tables from all schemas you need to first remark/delete the “where” clauses from the code.
declare @FK table (No int, tbl nvarchar(max), fk nvarchar(max), scm nvarchar(max))
declare @counter int
declare @sql nvarchar(max)
declare @table nvarchar(max)
declare @Constraint nvarchar(max)
declare @schemas nvarchar(max)
insert into @FK (No, tbl , fk, scm)
select ROW_NUMBER() OVER (ORDER BY f.name),t.name, f.name, s.name from sys.foreign_keys f inner join sys.tables t on f.parent_object_id=t.object_id inner join sys.schemas s on s.schema_id=t.schema_id where s.name=‘SCHEMA_NAME’
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 @schemas = (select scm from @FK where No=@counter)
set @sql = ‘ALTER TABLE [‘+@schemas+‘].[‘+@table+‘] DROP CONSTRAINT [‘+@Constraint+‘]’
exec SP_EXECUTESQL @sql
set @counter=@counter–1
end
delete from @FK
insert into @FK (No, tbl, fk, scm)
select ROW_NUMBER() OVER (ORDER BY t.name),t.name, fk=null, s.name from sys.tables t inner join sys.schemas s on s.schema_id=t.schema_id where s.name=‘SCHEMA_NAME’
set @counter= (select count(*) from @FK)
while @counter >0
begin
set @table = (select tbl from @FK where No=@counter)
set @sql = ‘DROP TABLE [‘+@schemas+‘].[‘+@table+‘]’
exec SP_EXECUTESQL @sql
set @counter=@counter–1
end;
