如何在MSSQL中更改所有表，视图和存储过程的架构 [英] How to change schema of all tables, views and stored procedures in MSSQL

问题描述

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

Recently we were having issues on our database server and after long efforts it was decided to change the database server. So we managed to restore the database on another server, change the connection string, etc. Everything was going as planned until we tried to access the website from a web browser.

我们开始遇到有关找不到数据库对象的错误.后来我们发现它是由于修改后的架构名称而发生的.由于Kentico数据库中有数百个数据库对象(表，视图和存储过程)，因此手动逐个更改所有对象是不可行的.有可行的方法吗?

We started getting errors about database objects not being found. Later we found out that it occured as a result of the modified schema name. Since there are hundreds of database objects (tables, views and stored procedures) in a Kentico database, it is not feasible to change all of them manually, one-by-one. Is there a practical way of doing this?

推荐答案

是的，有可能.

要更改数据库对象的架构，您需要运行以下SQL脚本:

To change the schema of a database object you need to run the following SQL script:

ALTER SCHEMA NewSchemaName TRANSFER OldSchemaName.ObjectName

其中ObjectName可以是表，视图或存储过程的名称.问题似乎在于获取具有给定的shcema名称的所有数据库对象的列表.值得庆幸的是，有一个名为sys.Objects的系统表可以存储所有数据库对象.以下查询将生成完成此任务所需的所有SQL脚本:

Where ObjectName can be the name of a table, a view or a stored procedure. The problem seems to be getting the list of all database objects with a given shcema name. Thankfully, there is a system table named sys.Objects that stores all database objects. The following query will generate all needed SQL scripts to complete this task:

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"表示存储过程.

Where type 'U' denotes user tables, 'V' denotes views and 'P' denotes stored procedures.

运行上面的脚本将生成将对象从一种模式转移到另一种模式所需的SQL命令.像这样:

Running the above script will generate the SQL commands needed to transfer objects from one schema to another. Something like this:

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;

现在您可以运行所有这些生成的查询以完成传输操作.

Now you can run all these generated queries to complete the transfer operation.

这篇关于如何在MSSQL中更改所有表，视图和存储过程的架构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！