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

User · Answer

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

User · Answer

Way to do it for an individual thing   alter schema dbo transfer jonathan MovieData

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

ms-help   MS SQLCC v9 MS SQLSVR v9 en tsqlref9 html 0a760138-460e-410a-a3c1-d60af03bf2ed htm  ALTER SCHEMA schema name TRANSFER securable name

User · Answer

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

User · Answer

I just posted this to a similar question  In sql server 2005  how do I change the  quot schema quot  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  lt  gt   dbo    UNION ALL  SELECT TABLE SCHEMA AS  schema   TABLE NAME AS  name  FROM INFORMATION SCHEMA TABLES  WHERE TABLE SCHEMA  lt  gt   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

User · Answer

USE MyDB  GO ALTER SCHEMA dbo TRANSFER jonathan MovieData  GO   Ref  ALTER SCHEMA

User · Answer

ALTER SCHEMA dbo TRANSFER jonathan MovieData    See ALTER SCHEMA   Generalized Syntax     ALTER SCHEMA TargetSchema TRANSFER SourceSchema TableName

User · Answer

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

[sql] How do I change db schema to dbo

Examples related to sql

Examples related to sql-server

Examples related to database

Examples related to sql-server-2008

Examples related to schema