In my earlier submit known as “The best way to drop tables database extensive utilizing T-SQL” I’ve defined that how we may delete all tables database extensive in SQL Server. However, what if there are another schemas aside from “dbo” and a few tables which might be created in these schemas? If we simply delete “[dbo].” from the code we’ll clearly face to the next error message: “Can’t discover the article “TABLE_NAME” as a result of it doesn’t exist otherwise you should not have permissions.” for these tables that aren’t below “dbo” schema. So, we have to retrieve all schemas and their associated tables. This method is admittedly useful once we are implementing an ETL course of that’s designed to delete the tables from specific schemas and recreate the tables and populate new knowledge. This can be a widespread method for staging to delete and recreate tables as an alternative of updating current tables as replace is admittedly pricey.
So, let’s write some codes.
Be aware: The next code is written to delete the tables below a selected schema. If you wish to use it to delete all tables from all schemas that you must first comment/delete the “the place” clauses from the code.
declare @FK desk (No int, tbl nvarchar(max), fk nvarchar(max), scm nvarchar(max))
declare @counter int
declare @sql nvarchar(max)
declare @desk nvarchar(max)
declare @Constraint nvarchar(max)
declare @schemas nvarchar(max)
insert into @FK (No, tbl , fk, scm)
choose ROW_NUMBER() OVER (ORDER BY f.identify),t.identify, f.identify, s.identify from sys.foreign_keys f inside be a part of sys.tables t on f.parent_object_id=t.object_id inside be a part of sys.schemas s on s.schema_id=t.schema_id the place s.identify=‘SCHEMA_NAME’
set @counter= (choose rely(*) from @FK)
whereas @counter >0
start
set @desk = (choose tbl from @FK the place No=@counter)
set @Constraint = (choose fk from @FK the place No=@counter)
set @schemas = (choose scm from @FK the place No=@counter)
set @sql = ‘ALTER TABLE [‘+@schemas+‘].[‘+@table+‘] DROP CONSTRAINT [‘+@Constraint+‘]’
exec SP_EXECUTESQL @sql
set @counter=@counter–1
finish
delete from @FK
insert into @FK (No, tbl, fk, scm)
choose ROW_NUMBER() OVER (ORDER BY t.identify),t.identify, fk=null, s.identify from sys.tables t inside be a part of sys.schemas s on s.schema_id=t.schema_id the place s.identify=‘SCHEMA_NAME’
set @counter= (choose rely(*) from @FK)
whereas @counter >0
start
set @desk = (choose tbl from @FK the place No=@counter)
set @sql = ‘DROP TABLE [‘+@schemas+‘].[‘+@table+‘]’
exec SP_EXECUTESQL @sql
set @counter=@counter–1
finish;
Associated
Supply hyperlink
