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 … More