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?

  • SQL Update Parent Table Field based on Multiple Child Table Rows
  • Is it possible to use a Stored Procedure as a subquery in SQL Server 2008?
  • Azure Federations are Deprecated. What are my options?
  • How to determine total number of open/active connections in ms sql server 2005
  • How to pass sqlparameter to IN()?
  • How to change the table name in visual studio 2013 in design mode?
  • Current result: jonathan.MovieData

    Desired result: dbo.MovieData

  • Search for a string across all the databases in SQL SERVER and list all the databases, tables and corresponding columns
  • Error: String or binary data would be truncated. The statement has been terminated
  • SQLServer: How to sort table names ordered by their foreign key dependency
  • Split a string with no delimiters into columns
  • How to check if database exist on Remote Server through SQL query
  • Query against a view under master database is much slower than query directly under specific database
  • 10 Solutions collect form web for “How do I change db schema to dbo”

    ALTER SCHEMA dbo TRANSFER jonathan.MovieData;
    

    See ALTER SCHEMA.

    Generalized Syntax:

    ALTER SCHEMA TargetSchema TRANSFER SourceSchema.TableName; 
    

    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.

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

    Ref: ALTER SCHEMA

    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
    

    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.

    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

    Way to do it for an individual thing:

    alter schema dbo transfer jonathan.MovieData

    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;
    

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

    ALTER SCHEMA schema_name TRANSFER securable_name

    Open SQL server as SA account and click on new query past the blow queries

    than 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]
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.