Alternative to sp_rename
Published: Nov 13, 2016
There are a few flaws with the sp_rename method when switching tables or even simply renaming them… note that these aren’t bugs, they’re simply annoyances encountered which can otherwise be avoided.

Let’s take a simple example and presume that we have a table, with primary key and separate index, which needs to be renamed…

Here is the table:

use tempDB
go

if object_id('myOriginalTable') is not null drop table myOriginalTable
go

create table myOriginalTable
(
  
id int identity,
  
myValue varchar(100),
  
constraint pk_myOriginalTable primary key clustered(id)
)
create index ix_myOriginalTable_myValue on myOriginalTable(myValue)
go


So now, what happens if we want to rename it?

Well that’s simple enough… we could do the following:

exec sp_rename 'myOriginalTable', 'myNewTable'


Done.

So what’s the problem?

Well, if you look carefully you can see that we were following the company standard naming convention and, although the sp_rename will happily cater for the table name change, the indexes and keys remain unchanged.

This means that our only option would be to drop and re-create these which could be very time consuming if it was a large table.

This is where we can use another option… Partition Switching.

Okay, you’re now going to say that we haven’t partitioned our table, but we don’t need to… every table, by default, is a single partition and therefore it is eligible for switching.

Therefore what we can now do is create our new table with correctly named indexes and keys, and simply switch the data from our old to new table:

use tempDB
go

if object_id('myNewTable') is not null drop table myNewTable
if object_id('myOriginalTable') is not null drop table myOriginalTable
go

create table myOriginalTable
(
  
id int identity,
  
myValue varchar(100),
  
constraint pk_myOriginalTable primary key clustered(id)
)
create index ix_myOriginalTable_myValue on myOriginalTable(myValue)
go

create table myNewTable
(
  
id int identity,
  
myValue varchar(100),
  
constraint pk_myNewTable primary key clustered(id)
)
create index ix_myNewTable_myValue on myOriginalTable(myValue)
go

insert into myOriginalTable
select 'Test'
go

begin transaction
   truncate table
myNewTable

  
alter table myOriginalTable
   switch
to myNewTable
commit transaction
go

select *
from myNewTable


It’s as simple as that. We have now not only renamed our table, but also maintained all indexes and key naming conventions.

Just something to consider if you ever find yourself needing to rename a table.
Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

Your SQL Trainer
Kevin Urquhart

I am a SQL Server DBA, Architect, Developer, and Trainer for SQL Training. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Kevin Urquhart
SQL Server Consultant, London

Categories


Archives


Copyright © 2018 London SQL Server Training Courses @ SQL Training | Website Design From Scott Heron