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

Question

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

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

Answer 1

ALTER SCHEMA NewSchama TRANSFER dbo.Table1

Answer 2

In SQL Server Management Studio:

1. Right click the table and select modify
   
2. On the properties panel choose the correct owning schema.

Answer 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

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

where ? is the table name.

Answer 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.

Answer 5

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.

Answer 6

Show all TABLE_SCHEMA by this select:

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:

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

Answer 7

Just Use This

ALTER SCHEMA dbo TRANSFER db_owner.Tablename