假设你正在进行一次数据库迁移,并发现自己在2012年后再次更改了Object Ownership 即Schema Ownership。这里有一些将所有对象所有权转移到DBO的快速逻辑。或者,你可以简单地将'dbo'改为另一个名字,但目前,我们将在这个例子中使用DBO。
Suppose you’re doing yet another database migration and find yourself again changing the Object Ownership aka Schema Ownership in the post 2012 world… Here’s some quick logic to transfer all object ownership to DBO. Alternatively you can simply change ‘dbo’ to another name, but for the moment; we’ll be using DBO in this example.
use mydatabase;
set nocount on
declare @set_new_schema varchar(max)
set @set_new_schema = ''
select @set_new_schema = @set_new_schema +
'alter schema dbo transfer ' + table_schema + '.' + table_name + '';' char(10)
from information_schema.tables where table_schema = 'MyOldUserName'
exec (@set_new_schema)
Next just run a quick query to confirm.
接下来只需运行快速查询即可确认。
select table_schema, table_name from information_schema.tables order by table_schema asc
If you’re looking to see whats statements are being run first; just run the following:
如果你想知道最先运行的是什么语句; 只需运行以下命令:
set nocount on
declare @set_new_schema varchar(max)
set @set_new_schema = ''
select @set_new_schema = @set_new_schema +
'alter schema dbo transfer ' + table_schema + '.' + table_name + '';' char(10)
from information_schema.tables where table_schema = 'MyOldUserName'
select (@set_new_schema) for xml path(''), type
Build Quality | Build History | ||||
---|---|---|---|---|---|
- 李聪明的数据库 Lee's Clever Data
- Mike的数据库宝典 Mikes Database Collection
- 李聪明的数据库 "Lee Songming"