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.
Article ID: 157, Created: June 22, 2015 at 5:32 AM, Modified: October 12, 2019 at 8:40 AM