Alterando o schema de objetos em lote

Não vou entrar aqui no mérito do que são os schemas e como especificamos (deixemos isso para um outro momento).
Quero apenas deixar o seguinte cenário:
Um usuário criou uma série de scripts e, ao aplicá-lo no banco, todos os objetos ficaram com o nome do usuário no schema: usuário.procedure, usuario.function, usuario.table

Como ajustar isso, sem que seja necessário abrir cada objeto individualmente e colocar no schema correto (vamos usar o schema dbo aqui, para simplificar as coisas)?

Para alterar o schema de um objeto, deve ser executado o seguinte comando:

ALTER SCHEMA SchemaOriginal TRANSFER SchemaDestino.Objeto

Agora, vamos executar isso em lote:

Opção 1: Cursor

DECLARE cursor_schema CURSOR FOR 

SELECT SPECIFIC_SCHEMA as 'schema', SPECIFIC_NAME AS 'name'
FROM INFORMATION_SCHEMA.routines
WHERE SPECIFIC_SCHEMA <> 'dbo' 
UNION ALL
SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA <> 'dbo'

DECLARE @schema sysname, 
 @name sysname, 
 @sql varchar(500) 

OPEN cursor_schema     
FETCH NEXT FROM cursor_schema INTO @schema, @name 

WHILE @@FETCH_STATUS = 0     
BEGIN 
 SET @sql = 'ALTER SCHEMA dbo TRANSFER [' + @schema + '].[' + @name +']'    
 PRINT @sql   
 EXEC (@sql)  
 FETCH NEXT FROM cursor_schema INTO @schema, @name     
END 

CLOSE cursor_schema     
DEALLOCATE cursor_schema

Opção 2: Concatenação e execução (abordagem que eu prefiro)

DECLARE @sql varchar(500) = ''
SELECT @sql = @sql + sql
FROM (
	SELECT 'ALTER SCHEMA dbo TRANSFER ['+ SPECIFIC_SCHEMA + '].['+ SPECIFIC_NAME + ']; ' as sql
	FROM INFORMATION_SCHEMA.routines
	WHERE SPECIFIC_SCHEMA <> 'dbo' 
	UNION ALL
	SELECT 'ALTER SCHEMA dbo TRANSFER ['+ TABLE_SCHEMA + '].['+ TABLE_NAME + '];'
	FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_SCHEMA <> 'dbo'
) TMP
exec(@sql)

Por hoje era isso… Espero que seja útil para você!

• PS: Sim, você poderia substituir a query na INFORMATION_SCHEMA pela query na sys.objects com join na sys.schemas, sem maiores problemas…

[]’s!