CODEWITHSUNDEEP
sqlsql-serverdatabasesql-server-2008schema
jonathan hall
jonathan hall

Reputation: 1396

How do I change db schema to dbo

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

Upvotes: 124

Views: 241723

Answers (11)

Rj Regalado
Rj Regalado

Reputation: 461

Use this code if you want to change all tables, views, and procedures at once.

BEGIN
DECLARE @name NVARCHAR(MAX)
        , @sql NVARCHAR(MAX)
        , @oldSchema NVARCHAR(MAX) = 'MyOldSchema'
        , @newSchema NVARCHAR(MAX) = 'dbo'
        
DECLARE objCursor CURSOR FOR
        SELECT  o.name
        FROM    sys.objects o
        WHERE   type IN ('P', 'U', 'V')

OPEN    objCursor
        FETCH NEXT FROM objCursor INTO @name
        WHILE   @@FETCH_STATUS = 0
        BEGIN
            SET @sql = 'ALTER SCHEMA '+ @newSchema +' TRANSFER '+ @oldSchema + '.' +  @name         
            BEGIN TRY
                exec(@sql)
                PRINT 'Success: ' + @sql
            END TRY
            BEGIN CATCH
                PRINT 'Error executing: ' + @sql
            END CATCH
            FETCH NEXT FROM objCursor INTO @name
        END
CLOSE   objCursor
DEALLOCATE  objCursor

END

It will execute a code like this

ALTER SCHEMA dbo TRANSFER MyOldSchema.xx_GetUserInformation

Upvotes: 0

MJ X
MJ X

Reputation: 9054

Open SQL Server as SA account and click on new query past the below queries

then click on execute, it will rollback all owned schema back to SA account

alter authorization on schema::[db_datareader] to [dbo]
alter authorization on schema::[db_datareader] to [db_datareader]
alter authorization on schema::[db_datawriter] to [dbo]
alter authorization on schema::[db_datawriter] to [db_datawriter]
alter authorization on schema::[db_securityadmin] to [dbo]
alter authorization on schema::[db_securityadmin] to [db_securityadmin]
alter authorization on schema::[db_accessadmin] to [dbo]
alter authorization on schema::[db_accessadmin] to [db_accessadmin]
alter authorization on schema::[db_backupoperator] to [dbo]
alter authorization on schema::[db_backupoperator] to [db_backupoperator]
alter authorization on schema::[db_ddladmin] to [dbo]
alter authorization on schema::[db_ddladmin] to [db_ddladmin]
alter authorization on schema::[db_owner] to [dbo]
alter authorization on schema::[db_owner] to [db_owner]

Upvotes: 2

Remus Rusanu
Remus Rusanu

Reputation: 294237

ALTER SCHEMA dbo TRANSFER jonathan.MovieData;

See ALTER SCHEMA.

Generalized Syntax: 

ALTER SCHEMA TargetSchema TRANSFER SourceSchema.TableName;

Upvotes: 195

Hannover Fist
Hannover Fist

Reputation: 10860

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;

Upvotes: 2

anar khalilov
anar khalilov

Reputation: 17498

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

Upvotes: 5

Neru-J
Neru-J

Reputation: 1623

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

Upvotes: 17

Lanceomagnifico
Lanceomagnifico

Reputation: 1303

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.

Upvotes: 10

Jon Onstott
Jon Onstott

Reputation: 13727

Way to do it for an individual thing:

alter schema dbo transfer jonathan.MovieData

Upvotes: 2

patmortech
patmortech

Reputation: 10219

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.

Upvotes: 45

Mitch Wheat
Mitch Wheat

Reputation: 300539

USE MyDB;
GO
ALTER SCHEMA dbo TRANSFER jonathan.MovieData;
GO

Ref: ALTER SCHEMA

Upvotes: 22

Reputation:

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/0a760138-460e-410a-a3c1-d60af03bf2ed.htm

ALTER SCHEMA schema_name TRANSFER securable_name

Upvotes: 1

Related Questions