主页 / dba / 问题 / 54610
Accepted
Matthew
Asked: 2013-12-07 12:51:45 +0800 CST

如何在 SQL Server 中批量迁移架构？

  • 772

我们目前有多个数据库，但希望将它们组合起来，而是使用模式分离我们的域上下文。

在 MS SQL Server 2008 R2 中，如何将一个架构的所有内容批量重定位到另一个架构中？

例如，我们在模式中创建的所有表、视图、过程、索引等......dbo现在都将存在于foo模式中。

编辑：我想根据 AaronBertrand 的精彩评论进行澄清。这不是多租户情况。我们的情况是内部工具插件是由没有将他们的表合并到工具数据库中的开发人员单独开发的。

sql-server-2008-r2 schema

1 个回答

  1. Best Answer

    基本概念实际上非常简单：您生成一个脚本sys.objects并sys.schemas构建ALTER SCHEMA TRANSFER语句。例如，您在dbo架构中有三个对象，并且您希望将它们全部移动到blat架构中：

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

    以下代码：

    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;

    将产生此脚本（但可能不是按此顺序）：

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

    （您可能想要添加额外的过滤器以排除dbo架构中您不想移动的对象，忽略某些对象类型（例如，可能您的所有函数都是实用函数并且不需要移动），生成按对象类型排序的脚本等）

    但是将所有对象移动到新模式有几个问题：

    1. 可能很多你的代码仍然会引用这些对象dbo.object- 除了蛮力之外，没有简单的方法可以解决这个问题。您可能dbo.很容易找到所有出现的 ，但这些也可能返回误报，例如EXEC dbo.sp_executesql，dbo.在评论中，对保留在dbo.模式中的对象的真实引用等。

    2. 您的依赖项可能会完全失控，但我还没有彻底测试过。我确实知道在这种情况下：

      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;

      外键的迁移实际上比我预期的要顺利（需要注意的是，我正在测试的版本比你更新的版本）。但是因为里面的代码blat.pX还是引用dbo.bar了，显然是在执行这个过程：

      EXEC blat.pX;

      将产生此错误：

      消息 208，级别 16，状态 1，过程 pX
      对象名称“dbo.bar”无效。

      以及依赖查询，例如：

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

      将产生此错误：

      消息 2020，级别 16，状态 1
      为实体“blat.pX”报告的依赖关系可能不包括对所有列的引用。这要么是因为实体引用了一个不存在的对象，要么是因为实体中的一个或多个语句中的错误。在重新运行查询之前，请确保实体中没有错误并且该实体引用的所有对象都存在。

      并查询视图：

      SELECT a, barA FROM blat.vFooBar;

      产生这些错误：

      消息 208，级别 16，状态 1，过程 vFooBar
      无效的对象名称“dbo.foo”。
      消息 4413，级别 16，状态 1
      由于绑定错误，无法使用视图或函数“blat.vFoobar”。

      因此，这可能涉及大量清理工作。在您修复了所有对象引用之后，您可能需要重新编译所有模块并刷新新模式中的所有视图。您可以为此生成一个与上面的示例非常相似的脚本。

    • 9

相关问题