在 sqlserver2005中，如何在不丢失任何数据的情况下更改表的“模式”？

我有一个进入“ db _ owner”模式的表，我需要它在“ dbo”模式中。

是否有一个脚本或命令运行来切换它？

71921 次浏览
小开 
ALTER SCHEMA [NewSchema] TRANSFER [OldSchema].[Table1]
小开
最佳答案

在 SQLServerManagementStudio 中:

  1. 右键单击表并选择修改(现在称为“设计”)
  2. 在属性面板上选择正确的所属架构。
小开

您需要首先停止到数据库的所有连接，通过运行命令更改属于“ db _ owner”的表的所有权

sp_MSforeachtable @command1="sp_changeobjectowner ""?"",'dbo'"

在哪里? 是表的名称。

小开

答案很简单

sp_changeobjectowner [ @objname = ] 'object' , [ @newowner = ] 'owner'

您不需要停止所有到数据库的连接，这可以在运行中完成。

小开

当我使用 SQLManagementStudio 时，我没有得到“修改”选项，只有“设计”或“编辑”选项。如果您使用 Visual Studio (我已经检查了 VS.NET 2003,2005和2008) ，您可以使用服务器资源管理器来更改模式。右键单击表格并选择“设计表格”(2008)或“打开表格定义”(2003,2005)。突出显示完整的“列名”列。然后可以右键单击并选择“属性页”或“属性(2008)”。在属性表中，您应该看到“ Owner”(2003 & 2005)或“ Schema”(2008) ，其中包含可能的 Schema 的下拉列表。

小开

通过这个选择显示所有 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
小开

我使用它来处理一组表需要位于不同模式(在本例中是 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 )
;
小开

对赛义德的优秀答案 轻微的的改进。

我添加了一个 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

我也不得不还原一个 dbdump，结果发现这个模式不是 dbo ——我花了几个小时试图让 Sql Server 管理工作室或者可视化工作室的数据传输来改变目标模式... ... 最后我只能在新服务器上的还原转储上运行这个程序，以得到我想要的结果。


提交答案