Rename Table Without Stored Procedure

Question

Post reply

Rename Table Without Stored Procedure

amrata88

SSC Enthusiast

Points: 196
More actions
July 20, 2010 at 12:52 pm

#223510

Hi,
I want to rename the table without using a stored procedure.
Possible solution:
Create copy of the old table and give it a new required name. Drop the old table.
Is there any other optimized way to rename the table?
Amrata Agrawal

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ben Teraberry SSChampion Points: 11845 More actions · Answer 1

Why don't you want to use a stored procedure?

└> bt

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

amrata88 SSC Enthusiast Points: 196 More actions · Answer 2

Hi,

1. Using Stored Procedure requires permission .

Only the members of the sysadmin fixed server role, the db_owner fixed database role, or the owner of the object can execute sp_rename.

2. Renaming table using sp_rename does not renames all the references made to the table.

3. Out of curiosity.

Ben Teraberry SSChampion Points: 11845 More actions · Answer 3

Please stop with the random bolds ... really annoying.

However you rename the table you are going to need to take care of the references to the table. Personally, I would probably script out the new table and data insertions and then update all the dependencies. There are 3rd party tools that make all of this much easier.

└> bt

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Ram:) SSCrazy Points: 2246 More actions · Answer 4

I guess, You cannot change table name with out permissions, though there is a way to change table name other than sp_rename..

🙂

Jeff Moden SSC Guru Points: 1004408 More actions · Answer 5

amrata88 (7/20/2010)
Hi,
1. Using Stored Procedure requires permission .
Only the members of the sysadmin fixed server role, the db_owner fixed database role, or the owner of the object can execute sp_rename.
2. Renaming table using sp_rename does not renames all the references made to the table.
3. Out of curiosity.

I'd be curious as to why you want to rename a table but don't have permissions to do so. The only way to do this correctly without permissions is to get the System DBA involved.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)