我有一张表格被放在了"db_owner"模式下,我需要将它转移到"dbo"模式下。是否有脚本或命令可以切换它?
ALTER SCHEMA [NewSchema] TRANSFER [OldSchema].[Table1]
通过此选择显示所有的TABLE_SCHEMA
:
SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
您可以使用此查询将所有表的模式更改为dbo表模式:
DECLARE cursore CURSOR FOR
SELECT TABLE_SCHEMA, TABLE_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
FETCH NEXT FROM cursore INTO @schema, @tab
END
CLOSE cursore
DEALLOCATE cursore
PRINT @sql
应该改为 EXEC(@sql)
。 - tharensp_changeobjectowner [ @objname = ] 'object' , [ @newowner = ] 'owner'
你不需要停止所有与数据库的连接,这可以在运行时完成。
对于sAeid的优秀答案,我进行了轻微改进...
我添加了一个exec来使代码自动执行,并在顶部添加了一个union,以便我可以更改两个表和存储过程的模式:
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
我也需要恢复一个数据库转储,但发现模式不是dbo - 我花了几个小时尝试使用Sql Server管理工具或Visual Studio数据传输来更改目标模式...最终我只是在新服务器上对恢复的转储运行了这个命令,以达到我想要的效果。
我使用这个方法来解决需要在不同模式下使用一堆表的情况,例如在dbo模式下。
declare @sql varchar(8000)
;
select
@sql = coalesce( @sql, ';', '') + 'alter schema dbo transfer [' + s.name + '].[' + t.name + '];'
from
sys.tables t
inner join
sys.schemas s on t.[schema_id] = s.[schema_id]
where
s.name <> 'dbo'
;
exec( @sql )
;
sp_MSforeachtable @command1="sp_changeobjectowner ""?"",'dbo'"
其中 ? 是表名。