------------------------------------------------------------
-- Set the default schema for each user to the dbo schema --
-- because this was the way of working in SQL2000		  --
------------------------------------------------------------
DECLARE @DefaultSchemaCursor AS CURSOR
DECLARE @UserName AS NVARCHAR(128)
DECLARE @CurrentDefaultSchema AS NVARCHAR(128)

SET @DefaultSchemaCursor = CURSOR LOCAL FORWARD_ONLY READ_ONLY STATIC FOR

	SELECT	u.name AS [Name]
	,		default_schema_name
	FROM sys.database_principals AS u
	WHERE (u.type in ('U', 'S', 'G', 'C', 'K'))
	AND(	
			CAST(
					CASE 
						WHEN u.principal_id < 5 OR u.principal_id = 16382 OR u.principal_id = 16383 THEN 1 
						ELSE 0 
					END AS bit
				 ) = 0
		)
	AND default_schema_name <> 'dbo'
	ORDER BY [name]

OPEN @DefaultSchemaCursor
FETCH NEXT FROM @DefaultSchemaCursor INTO @UserName, @CurrentDefaultSchema
WHILE @@FETCH_STATUS = 0
BEGIN
	
	PRINT 'ALTER USER [' + @UserName + '] WITH DEFAULT_SCHEMA = dbo'

FETCH NEXT FROM @DefaultSchemaCursor INTO @UserName, @CurrentDefaultSchema
END
CLOSE @DefaultSchemaCursor
DEALLOCATE @DefaultSchemaCursor

---------------------------------------------------
-- Retrieving the Schemas which are user defined --
---------------------------------------------------
DECLARE @SchemaCursor AS CURSOR
DECLARE @SchemaName AS NVARCHAR(128)

SET @SchemaCursor = CURSOR LOCAL FORWARD_ONLY READ_ONLY STATIC FOR
	
	SELECT s.name AS [Schema_Name]
	FROM sys.schemas AS s
	WHERE (
			(	s.schema_id > 4 
				and s.schema_id < 16384
			) 
			or 
			(
				s.schema_id > 16400
			)
		  )

OPEN @SchemaCursor
FETCH NEXT FROM @SchemaCursor INTO @SchemaName
WHILE @@FETCH_STATUS = 0
BEGIN

	PRINT 'DROP SCHEMA [' + @SchemaName + ']'		

FETCH NEXT FROM @SchemaCursor INTO @SchemaName
END
CLOSE @SchemaCursor
DEALLOCATE @SchemaCursor