CODEWITHSUNDEEP
sql-serverdatabase
DevelopingChris
DevelopingChris

Reputation: 40788

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?

Upvotes: 76

Views: 72492

Answers (8)

Stephen Wrighton
Stephen Wrighton

Reputation: 37819

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

Upvotes: 85

Lanceomagnifico
Lanceomagnifico

Reputation: 1303

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.

Upvotes: 6

Restore The Data Dumps Again
Restore The Data Dumps Again

Reputation: 39325

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.

Upvotes: 87

Oliver
Oliver

Reputation: 668

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

Upvotes: 2

sAeid mOhammad hAshem
sAeid mOhammad hAshem

Reputation: 121

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

Upvotes: 12

Craig
Craig

Reputation: 105

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.

Upvotes: -3

Anthony K
Anthony K

Reputation: 2603

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.

Upvotes: 4

Jeremy
Jeremy

Reputation: 326

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.

Upvotes: 7

Related Questions