Rename Table and Change Schema with SP_RENAME

In this post “SP_RENAME table with schema change” I will share a trick to rename a table using sp_rename and then transfer it schema. As we know that we can use SP_RENAME system stored procedure to rename user created objects like tables, procedures, functions, views, indexes, columns, user defined types, CLR user defined types etc in current database.

For example, if you have a table with name “TestTableA” in schema “dbo” and want to change its name to “TestTableB”, you can use below command;

SP_RENAME 'dbo.TestTableA', 'TestTableB'

But in case you want to change the table name with schema change using procedure “sp_rename”, putting “Schemaname.Tablename” as new name in second parameter, will not work. What will happen if we put “Schemaname.Tablename” as new name and why it will happen, I have explained it in next section of this blog post.

Schema.TableName as New Name in sp_rename will not work

In case you want to rename the table with schema change, you cannot do as below;

SP_RENAME 'dbo.TestTableA', 'Person.TestTableB'

It will change the name of table from “TestTableA” to “[Person.TestTableB]”. Note that the schema is still “dbo” and not “Person”, but the table name has been changed to “[Person.TestTableB]”. You can verify the existence of the object by running a select statement as below;

SELECT * FROM dbo.[Person.TestTableB]

Below is the syntax of sp_rename procedure which clearly defines that the new name for the object must be a one part name.

SP_RENAME syntax:

sp_rename [ @objname = ] ‘object_name’ , [ @newname = ] ‘new_name'[ , [ @objtype = ] ‘object_type’ ]

Below are the details of each parameter in regards of table renaming;

object_name: Qualified or non qualified name of the table. It can be Schema.TableName or Database.Schema.TableName. But in case of Datbase name is provided, it must be the current database otherwise you will get the below error message.

new_name: New name must be a one part name. We can not use schema or Database name here.

object_type: Optional and defaults to NULL. We can set the type of object being renamed here. It can be one of these values “COLUMN, DATABASE, INDEX, OBJECT, STATISTICS and USERDATATYPE”.

For more details you can visit this link MSDN.

Change Table Name with Schema change with the help of sp_rename

To change the schema and table name both, first we have to rename the table using SP_RENAME and then transfer the schema using ALTER SCHEMA command. Lets see this in action and for this demo I will use database “AdventureWorks2012”.

--Go to database AdventureWorks2012
USE AdventureWorks2012
GO

--Create a demo table
CREATE TABLE dbo.TestTableA
(
ID INT
)
GO

--Insert some demo data
INSERT INTO dbo.TestTableA
VALUES(1), (2), (3)

--Rename the table
EXEC SP_RENAME 'dbo.TestTableA', 'TestTableB'
GO

--Transfer the table from Schema dbo to Person
ALTER SCHEMA Person TRANSFER dbo.TestTableB
GO

Now check the existence of table with old name and schema as below;

SELECT * FROM dbo.TestTableA

Output:

Now check this with new name and schema;

SELECT * FROM Person.TestTableB

Conclusion

I have used SP_RENAME to rename the object first and then i have transferred the schema of table with ALTER SCHEMA statement.

Thanks for your reading and interest. In case you have some better idea to do this, feel free to share that in comment section. I welcome all the comments and suggestions at SQLRelease. Please don’t forget to share and rate this post if you really like this. I also request you to like our FaceBook page.