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.
