/*
Empty out a schema, useful for SunSystems where a user was not in dbo default schema!
Joe Scott, Lake Financial Systems
joe.scott@lake.co.uk
*/

DECLARE @sourceSchemaName sysname = 'DOMIAN\SunSystemsServices' --Schema you are emptying
DECLARE @newSchemaName sysname = 'dbo' --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