blocks|key|1500931|text|ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/0a760138-460e-410a-a3c1-d60af03bf2ed.htm|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1500932|ALTER+SCHEMA+schema_name+TRANSFER+securable_name|1500933|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/0a760138-460e-410a-a3c1-d60af03bf2ed.htm

ALTER SCHEMA schema_name TRANSFER securable_name

blocks|key|1500945|text|USE+MyDB;
GO
ALTER+SCHEMA+dbo+TRANSFER+jonathan.MovieData;
GO|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1500946|参考：ALTER+SCHEMA|unstyled|offset|length|1500947|entityMap|0|LINK|mutability|MUTABLE|url|http://msdn.microsoft.com/en-us/library/ms173423.aspx^0|0|3|C|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|R|8|@]|9|@$G|S|H|T|1|U]]|A|$]]|$1|I|3|-4|5|F|7|V|8|@]|9|@]|A|$]]]|J|$K|$5|L|M|N|A|$O|P]]]]

<pre><code>USE MyDB;
GO
ALTER SCHEMA dbo TRANSFER jonathan.MovieData;
GO
</code></pre>

Ref: <a href="http://msdn.microsoft.com/en-us/library/ms173423.aspx" rel="noreferrer">ALTER SCHEMA</a>

blocks|key|1501001|text|您可以运行以下命令，这将为您的所有数据库生成一组ALTER+sCHEMA语句：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1501002|SELECT+'ALTER+SCHEMA+dbo+TRANSFER+'+%2B+TABLE_SCHEMA+%2B+'.'+%2B+TABLE_NAME+
FROM+INFORMATION_SCHEMA.TABLES+WHERE+TABLE_SCHEMA+=+'jonathan'|code-block|syntax|javascript|1501003|然后，您必须在查询分析器中复制并运行这些语句。|1501004|这里有一个较旧的脚本，我想它也可以通过更改对象所有者来实现这一点。不过，我还没有在2008年尝试过。|1501005|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)%2B''.''%2BQUOTENAME(TABLE_NAME)+=+''?''
++++++AND+TABLE_SCHEMA+=+'''+%2B+@old+%2B+'''
++)
++EXECUTE+sp_changeobjectowner+''?'',+'''+%2B+@new+%2B+''''

EXECUTE+sp_MSforeachtable+@sql|1501006|从this+site那里拿到的。|offset|length|1501007|如果需要的话，它还讨论了如何对存储过程执行相同的操作。|1501008|entityMap|0|LINK|mutability|MUTABLE|url|http://weblogs.asp.net/owscott/archive/2004/01/30/SQL-Table-Ownership-Changes_2C00_-Quick-and-Easy.aspx^0|0|0|0|0|0|1|9|0|0|0^^$0|@$1|2|3|4|5|6|7|10|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|11|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|12|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|13|8|@]|9|@]|A|$]]|$1|K|3|L|5|D|7|14|8|@]|9|@]|A|$E|F]]|$1|M|3|N|5|6|7|15|8|@]|9|@$O|16|P|17|1|18]]|A|$]]|$1|Q|3|R|5|6|7|19|8|@]|9|@]|A|$]]|$1|S|3|-4|5|6|7|1A|8|@]|9|@]|A|$]]]|T|$U|$5|V|W|X|A|$Y|Z]]]]

You can run the following, which will generate a set of ALTER sCHEMA statements for all your talbes:

<pre><code>SELECT 'ALTER SCHEMA dbo TRANSFER ' + TABLE_SCHEMA + '.' + TABLE_NAME 
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'jonathan'
</code></pre>

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.

<pre><code>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
</code></pre>

Got it from <a href="http://weblogs.asp.net/owscott/archive/2004/01/30/SQL-Table-Ownership-Changes_2C00_-Quick-and-Easy.aspx" rel="noreferrer">this site</a>.

It also talks about doing the same for stored procs if you need to.

blocks|key|1501041|text|为一件事做的方式：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1501042|alter+schema+dbo+transfer+jonathan.MovieData|1501043|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

Way to do it for an individual thing:

alter schema dbo transfer jonathan.MovieData

blocks|key|1501123|text|您可以批量更改多个数据库对象的模式，如本文所述：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1501124|How+to+change+schema+of+all+tables,+views+and+stored+procedures+in+MSSQL|offset|length|1501125|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/questions/17571233/how-to-transfer-all-tables-views-and-stored-procedures-from-one-schema-to-anoth/17571234#17571234^0|0|0|20|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|O|8|@]|9|@$D|P|E|Q|1|R]]|A|$]]|$1|F|3|-4|5|6|7|S|8|@]|9|@]|A|$]]]|G|$H|$5|I|J|K|A|$L|M]]]]

You can batch change schemas of multiple database objects as described in this post:

<a href="https://stackoverflow.com/questions/17571233/how-to-transfer-all-tables-views-and-stored-procedures-from-one-schema-to-anoth/17571234#17571234">How to change schema of all tables, views and stored procedures in MSSQL</a>

blocks|key|1286339|text|我也有类似的问题，但我的模式中有一个反斜杠。在这种情况下，请在架构周围包括括号。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1286340|ALTER+SCHEMA+dbo+TRANSFER+[DOMAIN\jonathan].MovieData;|code-block|syntax|javascript|1286341|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

I had a similar issue but my schema had a backslash in it. In this case, include the brackets around the schema.

<pre><code>ALTER SCHEMA dbo TRANSFER [DOMAIN\jonathan].MovieData;
</code></pre>

blocks|key|1286404|text|以SA帐户的身份打开SQL+Server，然后单击以下查询后的新查询|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1286405|然后单击execute，它会将所有拥有的模式回滚到SA帐户|1286406|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]|code-block|syntax|javascript|1286407|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|L|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|M|8|@]|9|@]|A|$G|H]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Open SQL Server as SA account and click on new query past the below queries

then click on execute, it will rollback all owned schema back to SA account

<pre><code>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]
</code></pre>

blocks|key|1286476|text|如果要一次更改所有表、视图和过程，请使用此代码。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1286477|BEGIN
DECLARE+@name+NVARCHAR(MAX)
++++++++,+@sql+NVARCHAR(MAX)
++++++++,+@oldSchema+NVARCHAR(MAX)+=+'MyOldSchema'
++++++++,+@newSchema+NVARCHAR(MAX)+=+'dbo'
++++++++
DECLARE+objCursor+CURSOR+FOR
++++++++SELECT++o.name
++++++++FROM++++sys.objects+o
++++++++WHERE+++type+IN+('P',+'U',+'V')

OPEN++++objCursor
++++++++FETCH+NEXT+FROM+objCursor+INTO+@name
++++++++WHILE+++@@FETCH_STATUS+=+0
++++++++BEGIN
++++++++++++SET+@sql+=+'ALTER+SCHEMA+'%2B+@newSchema+%2B'+TRANSFER+'%2B+@oldSchema+%2B+'.'+%2B++@name+++++++++
++++++++++++BEGIN+TRY
++++++++++++++++exec(@sql)
++++++++++++++++PRINT+'Success:+'+%2B+@sql
++++++++++++END+TRY
++++++++++++BEGIN+CATCH
++++++++++++++++PRINT+'Error+executing:+'+%2B+@sql
++++++++++++END+CATCH
++++++++++++FETCH+NEXT+FROM+objCursor+INTO+@name
++++++++END
CLOSE+++objCursor
DEALLOCATE++objCursor|code-block|syntax|javascript|1286478|结束|1286479|它将执行如下代码|1286480|ALTER+SCHEMA+dbo+TRANSFER+MyOldSchema.xx_GetUserInformation|1286481|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|R|8|@]|9|@]|A|$]]|$1|K|3|L|5|D|7|S|8|@]|9|@]|A|$E|F]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

Use this code if you want to change all tables, views, and procedures at once.
<pre><code>BEGIN
DECLARE @name NVARCHAR(MAX)
 , @sql NVARCHAR(MAX)
 , @oldSchema NVARCHAR(MAX) = 'MyOldSchema'
 , @newSchema NVARCHAR(MAX) = 'dbo'
 
DECLARE objCursor CURSOR FOR
 SELECT o.name
 FROM sys.objects o
 WHERE type IN ('P', 'U', 'V')

OPEN objCursor
 FETCH NEXT FROM objCursor INTO @name
 WHILE @@FETCH_STATUS = 0
 BEGIN
 SET @sql = 'ALTER SCHEMA '+ @newSchema +' TRANSFER '+ @oldSchema + '.' + @name 
 BEGIN TRY
 exec(@sql)
 PRINT 'Success: ' + @sql
 END TRY
 BEGIN CATCH
 PRINT 'Error executing: ' + @sql
 END CATCH
 FETCH NEXT FROM objCursor INTO @name
 END
CLOSE objCursor
DEALLOCATE objCursor
</code></pre>
END
It will execute a code like this
<pre><code>ALTER SCHEMA dbo TRANSFER MyOldSchema.xx_GetUserInformation
</code></pre>

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: <code>jonathan.MovieData</code>. In the table <code>properties</code> it lists <code>jonathan</code> as the db schema. When I write stored procedures I now have to include <code>jonathan.</code> in front of all the table names which is confusing.

How do I change all my tables to be dbo instead of jonathan?

Current result: <code>jonathan.MovieData</code>

Desired result: <code>dbo.MovieData</code>

How do I change db schema to dbo

SQL Server

数据库

服务器

我将一堆表从旧的sql服务器(2000)导入到2008年的数据库中。所有导入的表都以我的用户名为前缀，例如：jonathan.MovieData。在表properties中，它将jonathan列为db模式。当我编写存储过程时，我现在必须在所有表名前面包含jonathan.，这很令人困惑。如何将我所有的表都改为dbo而...

问如何将db架构更改为dbo
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何将db架构更改为dboEN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何将db架构更改为dbo
EN