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
Navigator
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
Express Hyper-V Hosting
Dedicated Memory:
2GB
Disk Space:
120GB
Bandwidth:
Unmetered
Windows 2016/2012:
Free
Monthly:
$11.99
Dedicated SQL Server
CPU:
Quad-Core X3440 CPU
RAM:
16GB RAM
Disk:
2x120GB SSD + 300GB SATA
RAID:
RAID 1
Bandwidth:
Unmetered
Windows 2016/2012:
Free
Monthly:
$79.00