在sql server 2005中，如何在不丢失任何数据的情况下更改表的“模式”?

I have a table that got into the "db_owner" schema, and I need it in the "dbo" schema.

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

Is there a script or command to run to switch it over?

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

8 个解决方案

#1

In SQL Server Management Studio:

在SQL Server Management Studio中:

Right click the table and select modify (it's called "Design" now)
右击该表并选择modify(现在叫做“设计”)
On the properties panel choose the correct owning schema.
在属性面板上选择正确的拥有模式。

#2

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

#3

Show all TABLE_SCHEMA by this select:

通过此选择显示所有表_schema:

SELECT TABLE_SCHEMA, TABLE_NAME 
FROM INFORMATION_SCHEMA.TABLES

You can use this query to change all schema for all tables to dbo table schema:

您可以使用此查询将所有表的所有模式更改为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

#4

simple answer

简单的答案

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

you don't need to stop all connections to the database, this can be done on the fly.

您不需要停止到数据库的所有连接，这可以动态完成。

#5

A slight improvement to sAeid's excellent answer...

萨伊德出色的回答稍有改进……

I added an exec to have this code self-execute, and I added a union at the top so that I could change the schema of both tables AND stored procedures:

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

I too had to restore a dbdump, and found that the schema wasn't dbo - I spent hours trying to get Sql Server management studio or visual studio data transfers to alter the destination schema... I ended up just running this against the restored dump on the new server to get things the way I wanted.

我也必须恢复dbdump，并发现该模式不是dbo——我花了几个小时试图获得Sql Server management studio或visual studio数据传输来改变目标模式……最后，我只是针对新服务器上恢复的转储运行此操作，以按照我希望的方式获取内容。

#6

When I use SQL Management Studio I do not get the 'Modify' option, only 'Design' or 'Edit'. If you have Visual Studio (I have checked VS.NET 2003, 2005 & 2008) you can use the Server Explorer to change the schema. Right click on the table and select 'Design Table' (2008) or 'Open Table Definition' (2003, 2005). Highlight the complete "Column Name" column. You can then right click and select 'Property Pages' or Properties (2008). From the property sheet you should see the 'Owner' (2003 & 2005) or 'Schema' (2008) with a drop down list for possible schemas.

当我使用SQL Management Studio时，我没有“修改”选项，只有“设计”或“编辑”选项。如果您有Visual Studio(我检查了VS.NET 2003、2005和2008)，您可以使用服务器资源管理器来更改模式。右键单击该表，选择‘Design table’(2008)或‘Open table Definition’(2003,2005)。突出显示完整的“列名”列。然后，您可以右键单击并选择“属性页”或属性(2008)。在属性表中，您应该看到'Owner'(2003 & 2005)或'Schema'(2008)，以及可能的模式的下拉列表。

#7

I use this for situations where a bunch of tables need to be in a different schema, in this case the dbo schema.

我将此用于一些表需要位于不同模式中的情况，在本例中是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 )
;

#8

-3

You need to firstly stop all connections to the database, change the ownership of the tables that are 'db_owner' by running the command

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

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

where ? is the table name.

在哪里?是表名。

#1