/*
Empty out a schema, useful for aplications where a user was not in dbo default schema!
Joe Scott - joe.scott@digitaliandm.com
Digital Incite and Matter Ltd
*/

DECLARE @sourceSchemaName sysname = 'oldSchema' --Schema you are emptying
DECLARE @newSchemaName sysname = 'newSchema' --Schema you are loading to
DECLARE @tableName sysname 
DECLARE @sql nvarchar(1000)

DECLARE tidySchema cursor FOR
	SELECT name FROM sys.objects 
	WHERE (schema_id = schema_id(@schemaName))
	  AND TYPE IN ('U')
OPEN tidySchema
FETCH FROM tidySchema INTO @tableName
while @@FETCH_STATUS=0 BEGIN
	SET @sql = 'alter schema [' + @newSchemaName + '] transfer [' + @sourceSchemaName + '].[' + @tableName + ']'
	PRINT @sql
	--EXEC sp_executesql @sql
	FETCH NEXT FROM tidySchema INTO @tableName
END
CLOSE tidySchema
DEALLOCATE tidySchema