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
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.
'ALTER SCHEMA newschema TRANSFER ' + s.Name + '.' + o.Name
sys.Objects AS o
sys.Schemas AS s on o.schema_id = s.schema_id
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)|
|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|
|PC||Assembly (CLR) stored-procedure||UQ||UNIQUE_CONSTRAINT|
|PK||PRIMARY_KEY_CONSTRAINT||X||Extended stored procedure|
Wednesday, March 11, 2015 at 1:32:27 PM Coordinated Universal Time
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
Tuesday, March 17, 2015 at 2:14:48 PM Coordinated Universal Time
Ronak, the schema rename just renames the object references. It does not move any data.