如何在MSSQL中更改所有表、视图和存储过程的架构

Question

最近我们的数据库服务器出现了问题，经过长时间的努力，决定更换数据库服务器。所以我们设法将数据库恢复到另一台服务器上，更改了连接字符串等。一切都按计划进行，直到我们尝试从Web浏览器访问网站时发生了错误。

我们开始收到有关找不到数据库对象的错误。后来我们发现这是由于修改的模式名称导致的。由于在Kentico数据库中有数百个数据库对象（表、视图和存储过程），因此手动逐个更改所有这些对象是不可行的。是否有实用的方法可以解决这个问题？

- anar khalilov

---

你确定你没有设置用户的默认模式吗？ - Nick.McDermaid

这个问题已经快两年了，但据我所记，它与用户的默认模式无关。 - anar khalilov

哦！由于某种原因，它出现在列表的顶部！ - Nick.McDermaid

读者 - 还可以查看 Microsoft 的答案：https://support.managed.com/kb/a100/how-to-change-schema-of-mssql-tables-stored-procedures-and-views-all-at-the-same-time.aspx - Yogi

我最近也遇到了同样的问题。是什么原因导致在创建具有现有用户并为新实例分配特权的新数据库实例时对象模式被改变？ - Mohamed Iqzas

Answer 1

是的，这是可能的。

要更改数据库对象的模式，您需要运行以下SQL脚本：

ALTER SCHEMA NewSchemaName TRANSFER OldSchemaName.ObjectName

ObjectName可以是表、视图或存储过程的名称。问题似乎在于获取具有给定架构名称的所有数据库对象的列表。幸运的是，有一个名为sys.Objects的系统表，它存储了所有数据库对象。以下查询将生成所有所需的SQL脚本以完成此任务：

SELECT 'ALTER SCHEMA NewSchemaName TRANSFER [' + SysSchemas.Name + '].[' + DbObjects.Name + '];'
FROM sys.Objects DbObjects
INNER JOIN sys.Schemas SysSchemas ON DbObjects.schema_id = SysSchemas.schema_id
WHERE SysSchemas.Name = 'OldSchemaName'
AND (DbObjects.Type IN ('U', 'P', 'V'))

其中类型“U”表示用户表，“V”表示视图，“P”表示存储过程。

运行上述脚本将生成转移对象所需的SQL命令。类似于这样：

ALTER SCHEMA NewSchemaName TRANSFER OldSchemaName.CONTENT_KBArticle;
ALTER SCHEMA NewSchemaName TRANSFER OldSchemaName.Proc_Analytics_Statistics_Delete;
ALTER SCHEMA NewSchemaName TRANSFER OldSchemaName.Proc_CMS_QueryProvider_Select;
ALTER SCHEMA NewSchemaName TRANSFER OldSchemaName.COM_ShoppingCartSKU;
ALTER SCHEMA NewSchemaName TRANSFER OldSchemaName.CMS_WebPart;
ALTER SCHEMA NewSchemaName TRANSFER OldSchemaName.Polls_PollAnswer;

现在您可以运行所有这些生成的查询来完成转移操作。

- anar khalilov

---

不错的解决方案。我使用了“WHERE SysSchemas.Name <> 'dbo'”来列出所有非dbo绑定对象。 - Mackan

而“SO”表示序列。 - Edgar Carvalho

对于所有表格，请查看此链接和此链接，以便在单个语句中完成操作，希望能帮到某些人。 - Shaiju T

如果您想要传输函数，可以在列表中添加“FN”、“TF”。其中，“FN”代表标量函数，“TF”代表表函数。更多信息请参考：https://msdn.microsoft.com/zh-cn/library/ms177596.aspx - Will Wu

这个回答非常完美。谢谢@anar帮我省下了时间。 - Raju Paladiya

Answer 2

这是我运行的SQL，将我的数据库中所有表（分布在多个模式中）移动到“dbo”模式中：

DECLARE 
    @currentSchemaName nvarchar(200),
    @tableName nvarchar(200)

DECLARE tableCursor CURSOR FAST_FORWARD FOR 
SELECT TABLE_SCHEMA, TABLE_NAME
FROM information_schema.tables
ORDER BY 1, 2

DECLARE @SQL nvarchar(400)

OPEN tableCursor 
FETCH NEXT FROM tableCursor INTO @currentSchemaName, @tableName

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @SQL = 'ALTER SCHEMA dbo TRANSFER ' + @currentSchemaName + '.' + @tableName
    PRINT @SQL

    EXEC (@SQL)

    FETCH NEXT FROM tableCursor INTO @currentSchemaName, @tableName
END

CLOSE tableCursor 
DEALLOCATE tableCursor 

Phew!

- Mike Gledhill

Answer 3

您可以通过复制/粘贴以下脚本来处理所有对象

注意：您需要在脚本中更改模式名称！

DECLARE @OldSchema VARCHAR(200)
DECLARE @NewSchema VARCHAR(200)
DECLARE @SQL nvarchar(4000)
SET @OldSchema = 'dbo'
SET @NewSchema = 'Inf'

DECLARE tableCursor CURSOR FAST_FORWARD FOR 
    SELECT 'ALTER SCHEMA  ['+ @NewSchema +'] TRANSFER [' + SysSchemas.Name + '].[' + DbObjects.Name + '];' AS Cmd
    FROM sys.Objects DbObjects
    INNER JOIN sys.Schemas SysSchemas ON DbObjects.schema_id = SysSchemas.schema_id
    WHERE SysSchemas.Name = @OldSchema
    AND (DbObjects.Type IN ('U', 'P', 'V'))
OPEN tableCursor 
FETCH NEXT FROM tableCursor INTO  @SQL
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @SQL
    EXEC (@SQL)
    FETCH NEXT FROM tableCursor INTO  @SQL
END
CLOSE tableCursor 
DEALLOCATE tableCursor 
PRINT '*** Finished ***'

- Haseeb Ahmed

Answer 4

感谢您的提示。这是我的更新版本，我在输出中添加了换行符，并在SchemaName和ObjectName周围加上了括号，因为一些对象的名称中有“-”，而使用括号解决了该命名错误。

SELECT 'ALTER SCHEMA NewSchemaName TRANSFER [' + SysSchemas.Name + '].[' +      DbObjects.Name + '];'
+ CHAR(13)+ CHAR(10)+ 'GO '+ CHAR(13)+ CHAR(10)
FROM sys.Objects DbObjects
INNER JOIN sys.Schemas SysSchemas ON DbObjects.schema_id =     SysSchemas.schema_id
WHERE SysSchemas.Name = 'OldSchemaName'
AND (DbObjects.Type IN ('U', 'P', 'V'))

- Hank Freeman

Answer 5

这是我对anar和Hank的答案的看法。
我还更改了系统视图和列的名称，因为我正在使用SQL Server 2019，而且似乎微软已经改变了所有这些对象的首字母大小写。

SELECT
    'ALTER SCHEMA NewSchemaName TRANSFER [' + ss.name + '].[' + so.name + '];' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)
FROM
    sys.objects so
INNER JOIN sys.schemas ss ON
    so.schema_id = ss.schema_id
WHERE
    ss.name = 'OldSchemaName'
    AND (so.type IN ('U', 'P', 'V'))

- Maged Mostafa