How do I change db schema to dbo

Question

I imported a bunch of tables from an old sql server (2000) to my 2008 database. All the imported tables are prefixed with my username, for example: jonathan.MovieData. In the table properties it lists jonathan as the db schema. When I write stored procedures I now have to include jonathan. in front of all the table names which is confusing.

How do I change all my tables to be dbo instead of jonathan?

Current result: jonathan.MovieData

Desired result: dbo.MovieData

Answer 1

You can run the following, which will generate a set of ALTER sCHEMA statements for all your talbes:

SELECT 'ALTER SCHEMA dbo TRANSFER ' + TABLE_SCHEMA + '.' + TABLE_NAME 
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'jonathan'

You then have to copy and run the statements in query analyzer.

Here's an older script that will do that for you, too, I think by changing the object owner. Haven't tried it on 2008, though.

DECLARE @old sysname, @new sysname, @sql varchar(1000)

SELECT
  @old = 'jonathan'
  , @new = 'dbo'
  , @sql = '
  IF EXISTS (SELECT NULL FROM INFORMATION_SCHEMA.TABLES
  WHERE
      QUOTENAME(TABLE_SCHEMA)+''.''+QUOTENAME(TABLE_NAME) = ''?''
      AND TABLE_SCHEMA = ''' + @old + '''
  )
  EXECUTE sp_changeobjectowner ''?'', ''' + @new + ''''

EXECUTE sp_MSforeachtable @sql

Got it from this site.

It also talks about doing the same for stored procs if you need to.

Answer 2

You can batch change schemas of multiple database objects as described in this post:

How to change schema of all tables, views and stored procedures in MSSQL

Answer 3

I just posted this to a similar question: In sql server 2005, how do I change the "schema" of a table without losing any data?

---

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 4

Move table from dbo schema to MySchema:

 ALTER SCHEMA MySchema TRANSFER dbo.MyTable

Move table from MySchema to dbo schema:

 ALTER SCHEMA dbo TRANSFER MySchema.MyTable

Answer 5

ALTER SCHEMA dbo TRANSFER jonathan.MovieData;

See ALTER SCHEMA.

Generalized Syntax:

ALTER SCHEMA TargetSchema TRANSFER SourceSchema.TableName; 

Answer 6

I had a similar issue but my schema had a backslash in it. In this case, include the brackets around the schema.

ALTER SCHEMA dbo TRANSFER [DOMAIN\jonathan].MovieData;