Delete tables in a schema

deletetablesforschema.sql

-- 1. INTRODUCTION
/*
  This script drops all fk constraints and tables from a configured scema.
*/

/*---------------------------*/
/*    2. CONFIGURATION       */
/*---------------------------*/

 declare @schema varchar(52)  = 'dbo';

/*----------------------------*/
declare @looper integer = 1;
declare @tableName varchar(52);
declare @fkName varchar(52);
declare @DROPTABLESCRIPT varchar(1000);
declare @DROPCONSTRAINTSCRIPT varchar(1000);

declare @IterationDropFkConstraint TABLE (ROW_INDEX INT, CONSTRAINT_NAME varchar(52), TABLE_NAME varchar(52)) 
insert 
  into 
    @IterationDropFkConstraint
	select 
		ROW_NUMBER() over(order by CONSTRAINT_NAME) as ROW_INDEX, 
		CONSTRAINT_NAME, 
		TABLE_NAME 
	from 
		INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
  where 
    CONSTRAINT_TYPE = 'FOREIGN KEY' and 
    CONSTRAINT_SCHEMA = @schema

declare @iterationDropTableSet table(ROW_INDEX INT, TABLE_NAME varchar(16))
insert 
  into 
    @iterationDropTableSet 
	select 
		ROW_NUMBER() over(order by TABLE_NAME) as ROW_INDEX, 
		TABLE_NAME 
	from 
		INFORMATION_SCHEMA.TABLES 
  where 
    TABLE_SCHEMA = @schema;

declare @setToIterateOverFk int = (select count(*) from @IterationDropFkConstraint);
declare @setToIterateOverTables int = (select count(*) from @iterationDropTableSet);

if(@setToIterateOverFk = 0) 
BEGIN
	print 'No Fk to delete'
	goto deleteTables
END

print 'Delete foreign key constraints - START'
WHILE @looper <  @setToIterateOverFk
BEGIN
	set @fkName = (select CONSTRAINT_NAME from @IterationDropFkConstraint where ROW_INDEX = @looper);
	set @tableName = (select TABLE_NAME from @IterationDropFkConstraint where ROW_INDEX = @looper);
	set @DROPCONSTRAINTSCRIPT = 'alter table ' + @tableName + ' drop constraint ' + @fkName;
	print 'QUERY: ' + @DROPCONSTRAINTSCRIPT
	EXEC(@DROPCONSTRAINTSCRIPT)
	set @looper = @looper + 1
END
print 'Loops completed: ' + CAST(@looper as varchar(8))
print 'Delete foreign key constraints - END'

deleteTables:

set @looper = 1

if(@setToIterateOverTables = 0) 
BEGIN
	print 'No tables to delete'
	goto endOfScript
END

print 'Delete TABLES - START'
while @looper <= @setToIterateOverTables
BEGIN
	set @tableName = (select TABLE_NAME from @iterationDropTableSet where ROW_INDEX = @looper);
	set @DROPTABLESCRIPT = 'drop table ' + @tableName
	print 'QUERY: ' + @DROPTABLESCRIPT
	EXEC(@DROPTABLESCRIPT)
	set @looper = @looper + 1
END
print 'Loops completed: ' + CAST(@looper as varchar(8))
print 'Delete TABLES - END'

endOfScript:

SELECT *  FROM INFORMATION_SCHEMA.TABLES
SELECT *  FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS