Como migrar entre esquemas em massa no SQL Server?

O conceito básico é bastante simples: você gera um script a partir do sys.objectsqual sys.schemasconstrói ALTER SCHEMA TRANSFERinstruções. Por exemplo, você tem três objetos no dboesquema e deseja mover todos eles para o blatesquema:

Table: dbo.foo
Table: dbo.bar
View:  dbo.vFooBar

O seguinte código:

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'
  ALTER SCHEMA blat TRANSFER dbo.' + QUOTENAME(o.name) + ';'
FROM sys.objects AS o
INNER JOIN sys.schemas AS s
ON o.[schema_id] = s.[schema_id]
WHERE s.name = N'dbo';

PRINT @sql;
-- EXEC sp_executesql @sql;

Irá render este script (mas talvez não nesta ordem):

ALTER SCHEMA blat TRANSFER dbo.bar;
ALTER SCHEMA blat TRANSFER dbo.foo;
ALTER SCHEMA blat TRANSFER dbo.vFooBar;

(Você pode querer adicionar filtros adicionais para deixar de fora objetos no dboesquema que você não deseja mover, deixar de fora certos tipos de objetos (por exemplo, talvez todas as suas funções sejam funções utilitárias e não precisem ser movidas), gere o script ordenado por tipo de objeto, etc.)

Mas há alguns problemas em mover todos os seus objetos para um novo esquema:

1. Provavelmente, muito do seu código ainda fará referência a esses objetos como dbo.object- não há maneira fácil de corrigir isso, exceto força bruta. Você provavelmente pode encontrar todas as ocorrências de com dbo.bastante facilidade, mas elas também podem retornar falsos positivos, como EXEC dbo.sp_executesql, dbo.em comentários, referências verdadeiras a objetos que permanecem no dbo.esquema, etc.

2. Suas dependências provavelmente estarão completamente fora de sintonia, mas eu não testei isso completamente. Eu sei que neste cenário:

   CREATE SCHEMA blat AUTHORIZATION dbo;
   GO
   
   CREATE TABLE dbo.foo(a INT PRIMARY KEY);
   CREATE TABLE dbo.bar(a INT FOREIGN KEY REFERENCES dbo.foo(a));
   GO
   
   CREATE PROCEDURE dbo.pX AS
   BEGIN
     SET NOCOUNT ON;
     SELECT a FROM dbo.bar;
   END
   GO
   
   CREATE VIEW dbo.vFooBar
   AS
     SELECT foo.a, bar.a AS barA
       FROM dbo.foo 
       INNER JOIN dbo.bar
       ON foo.a = bar.a;
   GO
   
   ALTER SCHEMA blat TRANSFER dbo.foo;
   ALTER SCHEMA blat TRANSFER dbo.bar;
   ALTER SCHEMA blat TRANSFER dbo.pX;
   ALTER SCHEMA blat TRANSFER dbo.vFooBar;
   

   As chaves estrangeiras realmente migram mais suavemente do que eu esperava (com a ressalva de que estou testando em uma versão muito mais recente do que você). Mas como o código blat.pXainda faz referência dbo.bara , obviamente executando o procedimento:

   EXEC blat.pX;
   

   Vai dar esse erro:

   Msg 208, Nível 16, Estado 1, Procedimento pX
   Nome de objeto inválido 'dbo.bar'.

   E consultas de dependência, como:

   SELECT * FROM sys.dm_sql_referenced_entities('blat.pX', N'OBJECT');
   

   Irá gerar este erro:

   Msg 2020, Level 16, State 1
   As dependências relatadas para a entidade "blat.pX" podem não incluir referências a todas as colunas. Isso ocorre porque a entidade faz referência a um objeto que não existe ou devido a um erro em uma ou mais instruções na entidade. Antes de executar novamente a consulta, verifique se não há erros na entidade e se todos os objetos referenciados pela entidade existem.

   E consultando a view:

   SELECT a, barA FROM blat.vFooBar;
   

   Gera estes erros:

   Msg 208, Nível 16, Estado 1, Procedimento vFooBar
   Nome de objeto inválido 'dbo.foo'.
   Msg 4413, Nível 16, Estado 1
   Não foi possível usar a exibição ou a função 'blat.vFoobar' devido a erros de vinculação.

   Então, isso pode envolver muita limpeza. E depois de corrigir todas as referências de objeto, você provavelmente desejará recompilar todos os módulos e atualizar todas as exibições no novo esquema. Você pode gerar um script para isso bastante semelhante ao exemplo acima.