Change an objects schema in SQL Server

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.

AF	Aggregate function (CLR)		R	Rule (old-style, stand-alone)
C	CHECK_CONSTRAINT		RF	Replication-filter-procedure
D	DEFAULT_CONSTRAINT		S	SYSTEM_TABLE
F	FOREIGN_KEY_CONSTRAINT		SQ	SERVICE_QUEUE
FN	SQL_SCALAR_FUNCTION		SN	Synonym
FS	CLR_SCALAR_FUNCTION		TA	Assembly (CLR) DML trigger
FT	Assembly (CLR) table-valued function		TF	SQL table-valued-function
IF	SQL inline table-valued function		TR	SQL_TRIGGER
IT	INTERNAL_TABLE		TT	Table type
P	SQL_STORED_PROCEDURE		U	USER_TABLE
PC	Assembly (CLR) stored-procedure		UQ	UNIQUE_CONSTRAINT
PG	Plan guide		V	View
PK	PRIMARY_KEY_CONSTRAINT		X	Extended stored procedure