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

In SQL Server Management Studio:

1. Right click the table and select modify (it's called "Design" now)
   
2. On the properties panel choose the correct owning schema.

Answer 2

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

Answer 3

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

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:

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.

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

Answer 7

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

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

Answer 8

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.