Drop tables from selected schemas database wide

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=@counter1

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=@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.