How To Change SQL Server 2008/2005 Object Schema?

Problem

It is not easy to change SQL Server 2008/2005 object schema (tables, stored procedures and views) to "dbo". Here is a solution.

Solution

a. Execute the following SQL script in SQL Server Management Studio query window.

    SELECT 'ALTER SCHEMA dbo TRANSFER ' + s.Name + '.' + o.Name 
    FROM sys.Objects o 
    INNER JOIN sys.Schemas s on o.schema_id = s.schema_id 
    WHERE s.Name = 'yourschema'
    And (o.Type = 'U' Or o.Type = 'P' Or o.Type = 'V')
    

b. Copy the output to another query window and execute.

    ALTER SCHEMA dbo TRANSFER yourschema.vFindVistType
    ALTER SCHEMA dbo TRANSFER yourschema.vEditPatients
    ALTER SCHEMA dbo TRANSFER yourschema.T_Referrals
    ALTER SCHEMA dbo TRANSFER yourschema.T_Therapist
    ALTER SCHEMA dbo TRANSFER yourschema.vOpenRefs
    ALTER SCHEMA dbo TRANSFER yourschema.vTherapists 
    

c. Now all objects (tables, stored procedures and views) should be "dbo" schema.

Related Resources

• ALTER SCHEMA (Transact-SQL)
• Change all MSSQL objects from one owner to another owner.