Change Schema name on Tables and Stored Procedures in SQL Server

It maybe necessary to change schema of a data for a number of reasons. I generally find myself doing this when I am re-using a database. At the same time I would add a new login and a new user. To change the schema for all the tables in the database you simply run the following query:

SELECT 'ALTER SCHEMA NEW_SCEHEMA TRANSFER ' + s.Name + '.' + p.Name 
FROM sys.Tables p 
INNER JOIN sys.Schemas s ON p.schema_id = s.schema_id

This is produce a list of code (one line per table) which you would need to copy and paste into a Query Analyzer window and run. This will in turn change the schema of every table in the database.

The syntax to change the schema is:

ALTER SCHEMA NEW_SCEHEMA TRANSFER OLD_SCHEMA.TABLE_NAME

In order to change the schema of Stored Procedures you use the same code as above but change sys.Table to sys.Procedures.

It is also useful to know how to delete the old schema or even the user.

Delete Scheme

DROP SCHEMA SCHEMA_NAME

Delete User

DROP USER USER_NAME