table rename problem

Question

table rename problem

prudhivi9

Say Hey Kid

Points: 678
More actions
September 9, 2011 at 1:16 am

#256824

Hi all,
by mistake i have renamed a table named 'claims' to 'sp_columns'. but now i want to get the original name to back. but when i am trying to do that it is showing the below error..
can u help me to get it back
Msg 15225, Level 11, State 1, Procedure sp_rename, Line 332
No item by the name of 'sp_columns' could be found in the current database 'AdventureWorksDW', given that @itemtype was input as '(null)'.
thanks.
chandu

Viewing 15 posts - 1 through 15 (of 20 total)

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

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 1

Please specify the exact object names, old table name, new table name and the exact syntax that you are using.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

prudhivi9 Say Hey Kid Points: 678 More actions · Answer 2

old table name: claims

new table name: sp_columns

database: adventureworksdw

command: sp_rename 'sp_columns', 'claims'

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 3

Basically:

sp_RENAME '[OldTableName]' , '[NewTableName]'

http://blog.sqlauthority.com/2008/08/26/sql-server-how-to-rename-a-column-name-or-table-name/

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

prudhivi9 Say Hey Kid Points: 678 More actions · Answer 4

ya u r right,

wt my question is..

my table name is 'claims',it is in 'adventureworksdw' database..

by mistake i have renamed that 'claims' to 'sp_columns'

now the table name is 'sp_columns'

so now i want to get my original table name back

for that i used command like

sp_RENAME 'sp_columns','claims'

now i am getting error mesg like

Msg 15225, Level 11, State 1, Procedure sp_rename, Line 332

No item by the name of 'sp_columns' could be found in the current database 'AdventureWorksDW', given that @itemtype was input as '(null)'.

prudhivi9 Say Hey Kid Points: 678 More actions · Answer 5

ya u r right,

wt my question is..

my table name is 'claims',it is in 'adventureworksdw' database..

by mistake i have renamed that 'claims' to 'sp_columns'

now the table name is 'sp_columns'

so now i want to get my original table name back

for that i used command like

sp_RENAME 'sp_columns','claims'

now i am getting error mesg like

Msg 15225, Level 11, State 1, Procedure sp_rename, Line 332

No item by the name of 'sp_columns' could be found in the current database 'AdventureWorksDW', given that @itemtype was input as '(null)'.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 6

I could not find a work around but you can change it in SSMS.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

HowardW One Orange Chip Points: 29109 More actions · Answer 7

The OP did say it was by mistake. Does it work if you prefix it with dbo.tablename? E.g.:

sp_RENAME 'dbo.sp_columns','claims'

The problem is likely to do with the fact that sp_ is a system reserved prefix (for legacy reasons) and will look in the master database first. I'll see if I can recreate in a test env - does the rename work in the Management Studio UI (Right Click, Rename)?

HowardW One Orange Chip Points: 29109 More actions · Answer 8

Tried it out, also can't find a work-around (can't rename in the UI either). The only option I can find is to script the table, recreate it as the correct name, migrate data over then drop the misnamed one

Shivrudra SSCrazy Points: 2835 More actions · Answer 9

you used the system keword while renaming the table thus it is not possible to revert,script out the exisitng table,create new table and import data.

Regards,
Shivrudra W

prudhivi9 Say Hey Kid Points: 678 More actions · Answer 10

prudhivi9

Say Hey Kid

Points: 678

September 9, 2011 at 4:03 am

#1379095

Thanks to all...

prudhivi9 Say Hey Kid Points: 678 More actions · Answer 11

U r right, but it is working...

upto creating the table it is working but while to import data from sp_columns it is not working and showing error like

Msg 208, Level 16, State 3, Line 1

Invalid object name 'sp_columns'.

HowardW One Orange Chip Points: 29109 More actions · Answer 12

Ouch. This is looking tricky. You can't even refer to the object name in a select statement (even fully referenced). I've tried adding a synonym to it, which lets you create it, but gives an error at runtime, scripting the data from SSMS (through SMO) which also doesn't work.

I'm not seeing many other options other than restore from a backup at the moment, although someone else might be able to come up with a cunning plan

prudhivi9 Say Hey Kid Points: 678 More actions · Answer 13

prudhivi9

Say Hey Kid

Points: 678

September 9, 2011 at 5:54 am

#1379137

can any body solve this issue....

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 14

prudhivi9 (9/9/2011)
can any body solve this issue....

Post removed.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/