首页 / 知识

关于数据库：在sql server 2005中，如何更改表的“模式”而不丢失任何数据？

2023-04-16 23:20:00

In sql server 2005, how do I change the “schema” of a table without losing any data?

我有一个进入" db_owner"模式的表，并且在" dbo"模式中需要它。

是否有运行脚本或命令以进行切换？

1	ALTER SCHEMA [NewSchema] TRANSFER [OldSchema].[Table1]

在SQL Server Management Studio中：

右键单击该表，然后选择"修改"(现在称为"设计")

在属性面板上，选择正确的拥有架构。

通过此选择显示所有TABLE_SCHEMA：

1 2	SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES

您可以使用此查询将所有表的所有架构更改为dbo表架构：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

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

简单的答案

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

您无需停止与数据库的所有连接，这可以随时进行。

sAeid的出色回答略有改进...

我添加了一个exec以使该代码自动执行，并且在顶部添加了一个并集，以便可以更改两个表和存储过程的架构：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

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 Management Studio或Visual Studio数据传输来更改目标架构...我最终只是针对还原后的数据库运行了它在新服务器上转储以按我想要的方式进行操作。

当我使用SQL Management Studio时，没有"修改"选项，只有"设计"或"编辑"。如果您拥有Visual Studio(我已经检查了VS.NET 2003、2005和2008)，则可以使用服务器资源管理器更改架构。右键单击表格，然后选择"设计表格"(2008)或"打开表格定义"(2003、2005)。突出显示完整的"列名称"列。然后，您可以右键单击并选择"属性页"或"属性"(2008)。在属性表中，您应该会看到"所有者"(2003＆2005)或"模式"(2008)，以及可能的模式的下拉列表。

我将其用于一堆表需要处于不同架构(在这种情况下为dbo架构)的情况下。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

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 )
;

您需要首先停止与数据库的所有连接，通过运行以下命令更改" db_owner"表的所有权