SQL 如何将数据库架构更改为 dbo
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1146599/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I change db schema to dbo
提问by jonathan hall
I imported a bunch of tables from an old sql server (2000) to my 2008 database. All the imported tables are prefixed with my username, for example: jonathan.MovieData. In the table propertiesit lists jonathanas the db schema. When I write stored procedures I now have to include jonathan.in front of all the table names which is confusing.
我从旧的 sql server (2000) 导入了一堆表到我的 2008 数据库。所有导入的表都以我的用户名为前缀，例如：jonathan.MovieData. 在表中，properties它jonathan列为 db 模式。当我编写存储过程时，我现在必须jonathan.在所有令人困惑的表名前面包含。
How do I change all my tables to be dbo instead of jonathan?
如何将所有表更改为 dbo 而不是乔纳森？
Current result: jonathan.MovieData
当前结果： jonathan.MovieData
Desired result: dbo.MovieData
想要的结果： dbo.MovieData
回答by Remus Rusanu
ALTER SCHEMA dbo TRANSFER jonathan.MovieData;
See ALTER SCHEMA.
请参阅ALTER SCHEMA。
Generalized Syntax:
通用语法：
ALTER SCHEMA TargetSchema TRANSFER SourceSchema.TableName;
回答by patmortech
You can run the following, which will generate a set of ALTER sCHEMA statements for all your talbes:
您可以运行以下命令，这将为您的所有 talbe 生成一组 ALTER sCHEMA 语句：
SELECT 'ALTER SCHEMA dbo TRANSFER ' + TABLE_SCHEMA + '.' + TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'jonathan'
You then have to copy and run the statements in query analyzer.
然后，您必须在查询分析器中复制和运行语句。
Here's an older script that will do that for you, too, I think by changing the object owner. Haven't tried it on 2008, though.
这是一个较旧的脚本，它也可以为您做到这一点，我认为通过更改对象所有者。不过 2008 年还没试过。
DECLARE @old sysname, @new sysname, @sql varchar(1000)
SELECT
@old = 'jonathan'
, @new = 'dbo'
, @sql = '
IF EXISTS (SELECT NULL FROM INFORMATION_SCHEMA.TABLES
WHERE
QUOTENAME(TABLE_SCHEMA)+''.''+QUOTENAME(TABLE_NAME) = ''?''
AND TABLE_SCHEMA = ''' + @old + '''
)
EXECUTE sp_changeobjectowner ''?'', ''' + @new + ''''
EXECUTE sp_MSforeachtable @sql
Got it from this site.
从这个网站上得到的。
It also talks about doing the same for stored procs if you need to.
如果需要，它还谈到对存储过程执行相同的操作。
回答by Mitch Wheat
回答by Neru-J
Move table from dbo schema to MySchema:
将表从 dbo 模式移动到 MySchema：
ALTER SCHEMA MySchema TRANSFER dbo.MyTable
Move table from MySchema to dbo schema:
将表从 MySchema 移动到 dbo 模式：
ALTER SCHEMA dbo TRANSFER MySchema.MyTable
回答by Lanceomagnifico
I just posted this to a similar question: In sql server 2005, how do I change the "schema" of a table without losing any data?
我刚刚将其发布到了一个类似的问题：在 sql server 2005 中，如何更改表的“架构”而不丢失任何数据？
A slightimprovement to sAeid's excellent answer...
一个微小的改进赛义德的出色答卷......
I added an exec to have this code self-execute, and I added a union at the top so that I could change the schema of both tables AND stored procedures:
我添加了一个 exec 以使此代码自动执行，并在顶部添加了一个联合，以便我可以更改两个表和存储过程的架构：
DECLARE cursore CURSOR FOR
select specific_schema as 'schema', specific_name AS 'name'
FROM INFORMATION_SCHEMA.routines
WHERE specific_schema <> 'dbo'
UNION ALL
SELECT TABLE_SCHEMA AS 'schema', TABLE_NAME AS 'name'
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA <> 'dbo'
DECLARE @schema sysname,
@tab sysname,
@sql varchar(500)
OPEN cursore
FETCH NEXT FROM cursore INTO @schema, @tab
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'ALTER SCHEMA dbo TRANSFER [' + @schema + '].[' + @tab +']'
PRINT @sql
exec (@sql)
FETCH NEXT FROM cursore INTO @schema, @tab
END
CLOSE cursore
DEALLOCATE cursore
I too had to restore a dbdump, and found that the schema wasn't dbo - I spent hours trying to get Sql Server management studio or visual studio data transfers to alter the destination schema... I ended up just running this against the restored dump on the new server to get things the way I wanted.
我也不得不恢复 dbdump，发现模式不是 dbo - 我花了几个小时试图让 Sql Server 管理工作室或 Visual Studio 数据传输来改变目标模式......我最终只是针对恢复的模式运行它转储到新服务器上以获得我想要的方式。
回答by anar khalilov
You can batch change schemas of multiple database objects as described in this post:
您可以批量更改多个数据库对象的架构，如本文所述：
How to change schema of all tables, views and stored procedures in MSSQL
回答by Hannover Fist
I had a similar issue but my schema had a backslash in it. In this case, include the brackets around the schema.
我有一个类似的问题，但我的架构中有一个反斜杠。在这种情况下，请在架构周围包含括号。
ALTER SCHEMA dbo TRANSFER [DOMAIN\jonathan].MovieData;
回答by MJVM
Open SQL Server as SA account and click on new query past the below queries
打开 SQL Server 作为 SA 帐户，然后单击以下查询后的新查询
then click on execute, it will rollback all owned schema back to SA account
然后单击执行，它将所有拥有的架构回滚回 SA 帐户
alter authorization on schema::[db_datareader] to [dbo]
alter authorization on schema::[db_datareader] to [db_datareader]
alter authorization on schema::[db_datawriter] to [dbo]
alter authorization on schema::[db_datawriter] to [db_datawriter]
alter authorization on schema::[db_securityadmin] to [dbo]
alter authorization on schema::[db_securityadmin] to [db_securityadmin]
alter authorization on schema::[db_accessadmin] to [dbo]
alter authorization on schema::[db_accessadmin] to [db_accessadmin]
alter authorization on schema::[db_backupoperator] to [dbo]
alter authorization on schema::[db_backupoperator] to [db_backupoperator]
alter authorization on schema::[db_ddladmin] to [dbo]
alter authorization on schema::[db_ddladmin] to [db_ddladmin]
alter authorization on schema::[db_owner] to [dbo]
alter authorization on schema::[db_owner] to [db_owner]
回答by Jon Onstott
Way to do it for an individual thing:
为个人事情做的方法：
alter schema dbo transfer jonathan.MovieData
更改架构 dbo 传输 jonathan.MovieData
回答by Jon Onstott
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/0a760138-460e-410a-a3c1-d60af03bf2ed.htm
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/0a760138-460e-410a-a3c1-d60af03bf2ed.htm
ALTER SCHEMA schema_name TRANSFER securable_name
ALTER SCHEMA schema_name TRANSFER securable_name
