How to Change Schema of MS SQL Tables, Stored Procedures, and Views All at the Same Time

Sometimes changing database users changes who the database owner is for a table from dbo.tablename to badschema.tablename. This will also affect any stored procedures created under the tables with the incorect schema.  If you need to change the schema for these tables back to the default dbo schema follow the steps below.

These steps require you to have Microsoft SQL Server Management Studio installed on your computer.  If you do not have this installed Microsoft SQL Server Mangement Studio Express 2008 is available for free from Microsoft, and can be downloaded through this link: http://www.microsoft.com/download/en/details.aspx?id=22985

You should be well-versed in MS SQL if you are going to perform these steps. If you do not feel comfortable with this, please contact support for assistance. Please make sure to back up your site before making any database changes.  If you are a shared hosting customer you may do this through your Control Panel by following the steps outlined here: Back Up Your Website Using Plesk. If you are a dedicated server customeryou may back up your site either through your Control Panel, or directly through the Control Suite by following the steps here: How to Back Up a Domain Using Control Suite.
Part 1

1. Open Microsoft SQL Server Management Studio and log in.
2. Click the New Query button.
3. Paste the following script into the New Query box changing oldschema to the name of the current schema:
   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 = 'oldschema'

   And (o.Type = 'U' Or o.Type = 'P' Or o.Type = 'V')
4. Click Execute

This query will result in output in the Results box which looks similar to the output below:

ALTER SCHEMA dbo TRANSFER yourschema.Table1

ALTER SCHEMA dbo TRANSFER yourschema.Table2

ALTER SCHEMA dbo TRANSFER yourschema.Table3

ALTER SCHEMA dbo TRANSFER yourschema.Table4

ALTER SCHEMA dbo TRANSFER yourschema.Table5

ALTER SCHEMA dbo TRANSFER yourschema.Table6

Part 2

1. Click the New Query button.
2. Paste the queries from the output of Part 1 into your new Query box
3. Click Execute

This will transfer the schema name across to the new schema, also changing the stored procedures and views.