While its not something you have to do often, sometimes you may be required to change the schema of an object in SQL Server such as a table or stored procedure. Last week I had to do this for the second time so I figured it was worthy of a blog post.

Now it is possible to do this through the SQL Management Studio GUI but if you have to do this for more than a few tables at once then it becomes quite a chore. In any case I prefer to script any changes to a database so here is how you go about it.

Script to change schema

The script to change the schema of an object is sql server is super simple.

ALTER SCHEMA newschema TRANSFER oldschema.tableName

This works just as well for other objects such as stored procedures or views.

ALTER SCHEMA newschema TRANSFER oldschema.procName
ALTER SCHEMA newschema TRANSFER oldschema.viewName

Caveat's

When you are doing this you may encounter problems with foreign key references and other dependencies. The first time I did this on a large number of tables I had to put in extra code to drop and rebuild multiple dependencies around the actual schema rename commands.

Also be aware that while the objects themselves will get updated, any stored procedures or adhoc queries will have to be updated accordingly. Also any diagrams will need to be updated as well.

Generate Transfer SQL

Finally here is a helper script I found to generate the rename scripts for you should you have a large number of objects to rename. Here I am generating scripts for tables, stored procedures and views.

SELECT 
      'ALTER SCHEMA newschema TRANSFER ' + s.Name + '.' + o.Name 
FROM
      sys.Objects AS o 
INNER JOIN 
      sys.Schemas AS s on o.schema_id = s.schema_id 
WHERE 
      s.Name = 'oldschema'
      And (o.Type = 'U' Or o.Type = 'P' Or o.Type = 'V')

SQL Server System Objects Reference

For your reference here is a table of the different sql server system object types.