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

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?

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.

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.

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.

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

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

In SQL Server Management Studio:

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

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.

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