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:
|
1 |
ALTER SCHEMA SchemaOriginal TRANSFER SchemaDestino.Objeto |
Agora, vamos executar isso em lote:
Opção 1: Cursor
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
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)
|
1 2 3 4 5 6 7 8 9 10 11 12 |
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!