How To Change SQL Server 2008/2005 Object Schema?


It is not easy to change SQL Server 2008/2005 object schema (tables, stored procedures and views) to "dbo". Here is a 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


Other Knowledgebase Articles

Basic SQL Hosting

# of Domains: 4
# of SQL Server Databases: 4
Disk Space: 50GB
Bandwidth: Unmetered
SQL Server 2016
Monthly: $4.99
More Information About Basic Shared SQL Server Hosting Plan

Express Hyper-V Hosting

Dedicated Memory:2GB
Disk Space: 120GB
Bandwidth: Unmetered
Windows 2016/2012: Free
Monthly: $11.99
More Information About Express Windows Hyper-V Server

Dedicated SQL Server

CPU: Quad-Core X3440 CPU
Disk: 2x120GB SSD + 300GB SATA
Bandwidth: Unmetered
Windows 2016/2012: Free
Monthly: $79.00
More Information About Dedicated SQL Server with SSD
Kubernetes Hosting | Windows VPS Hosting | SQL Server Hosting | ASP.NET Hosting | Windows Hyper-V Hosting | Dedicated SQL Server Hosting | Dedicated Windows Hosting | DNN Hosting
Cloud Application Hosting | MongoDB Hosting | CockroachDB Hosting | MySQL Hosting | MariaDB Hosting | Wordpress Hosting | Magento Hosting | Drupal Hosting | PostgreSQL Hosting | OpenCart Hosting | Redis Hosting | SQL Server Cloud Hosting