Author: Steven Neiland
Published:

Warning: This blog entry was written two or more years ago. Therefore, it may contain broken links, out-dated or misleading content, or information that is just plain wrong. Please read on with caution.

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

What Do You Think?

Reader Comments

Ronak's Gravatar
Ronak
Wednesday, March 11, 2015 at 1:32:27 PM EDT

Nothing is missing in this blog. Blog is very useful for me. I have one question. Any affect on performance or transfer schema process if we transfer schema of large table like 25+ million rows/3GB table? Thanks for help

Steven Neiland's Gravatar
Steven Neiland
Tuesday, March 17, 2015 at 2:14:48 PM EDT

Ronak, the schema rename just renames the object references. It does not move any data.

Post a Comment

Comment Etiquette:

  • Please keep comments on-topic.
  • Please do not post unrelated questions or large chunks of code.
  • Please do not engage in flaming/abusive behaviour.
  • Comments that contain or appear to be advertisments, will not be published.
  • Comments that appear to be created for the purpose of linkbuilding to commercial sites will be removed.

We are all adults here so play nice.

*
*



Archives Blog Listing

Tag Listing

Learn CF In A Week

Treehouse

 
Fork me on GitHub