How To Change SQL Server 2008/2005 Object Schema?

This is a solution that has been posted at:

http://www.asktolearnit.com/archive/752/how-to-change-default-schema-sql-server-2008.html

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.

Add Feedback