Drop a schema with all of its tables in SQL Server. Note that this won't clean up objects other than tables.

drop_schema_cascade.sql

-- DROP THE CONSTRAINTS

DECLARE @database nvarchar(50);
DECLARE @schemaName nvarchar(50);
DECLARE @sql nvarchar(300);
SET @database = ''; -- set the catalog name
SET @schemaName = ''; -- set the database name

SELECT @sql = 'USE ' + @database +';';
EXEC sp_executesql @sql;

DECLARE @constraints_to_remove TABLE (
	schemaName nvarchar(128) NOT NULL,
	tableName sysname NOT NULL,
	constraintName sysname NOT NULL);
 
INSERT @constraints_to_remove (
	schemaName,
	tableName,
	constraintName)
SELECT
	CONSTRAINT_SCHEMA,
	TABLE_NAME,
	CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE
	CONSTRAINT_SCHEMA = @schemaName
	AND CONSTRAINT_CATALOG = @database;
 
SELECT * FROM @constraints_to_remove;
 
DECLARE command_iterator CURSOR FAST_FORWARD FOR
	SELECT sqlstring =
		'ALTER TABLE [' + ctr.schemaName + '].[' + ctr.tableName + '] DROP CONSTRAINT [' + ctr.constraintName + ']' 
	FROM @constraints_to_remove ctr
 
	OPEN command_iterator
		FETCH NEXT FROM command_iterator
		INTO @sql
		WHILE @@FETCH_STATUS = 0
			BEGIN
				EXEC(@sql)
					 FETCH NEXT FROM command_iterator
					 INTO @sql
			END
CLOSE command_iterator
DEALLOCATE command_iterator

-- DROP THE TABLES

SELECT
	'DROP TABLE [' + TABLE_SCHEMA + '].[' + TABLE_NAME + '] '
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_CATALOG = @database AND TABLE_SCHEMA = @schemaName;

WHILE EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_CATALOG = @database and TABLE_SCHEMA = @schemaName)
BEGIN
	SELECT
		@sql = 'DROP TABLE [' + TABLE_SCHEMA + '].[' + TABLE_NAME + '] '
	FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_CATALOG = @database AND TABLE_SCHEMA = @schemaName;
	EXEC sp_executesql @sql
END
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_CATALOG = @database and TABLE_SCHEMA = @schemaName;

-- DROP THE SCHEMA
SELECT @sql = 'DROP SCHEMA ' + @schemaName +';';
EXEC sp_executesql @sql;