sp_rename error

• amrinea

  SSC Eights!

  Points: 942

  More actions

  May 17, 2010 at 11:26 am

  #140453

  I incorrectly ran sp_rename in the following manner:

  sp_rename 'Schema.Table', 'Schema.Table2'

  Now the table is no longer accessible. It shows up in sysobjects as Schema.Table2 in the "name" column. I know I shouldn't have included the Schema name in the second part of the rename, but now that I have, it's inaccessible. Is there a way that I can repair my mistake. I've tried doing:

  sp_rename 'Schema.Table2', 'Table', 'object'

  But I get there error:

  Either the parameter @objname is ambiguous or the claimed @objtype (object) is wrong.

• Steve Jones - SSC Editor

  SSC Guru

  Points: 738337

  More actions

  May 17, 2010 at 11:38 am

  #1168379

  Everything is in a schema. Likely it was in the dbo schema, so change it back to that one.

• Lowell

  SSC Guru

  Points: 323527

  More actions

  May 17, 2010 at 11:51 am

  #1168386

  ok this was kewl to know;

  once you doink up an object name so it has a dot in it, you need your brackets around the object, and it was easier for me to visualize with three part naming convensions to undo the damage;

  CREATE SCHEMA bob

  GO

  CREATE TABLE bob.Test (

  testid int,stuff varchar(50) )

  GO

  exec sp_rename 'bob.Test', 'bob.Test2' --created bob.[bob.Test2]

  --try to fix, this fails no object found:

  exec sp_rename 'bob.bob.Test2'

  --works, due to brackets

  exec sp_rename 'bob.[bob.Test2]'

  --select from the object with 3 part naming:

  select * from [SandBox].[bob].[bob.Test2]

  --rename the object successfully

  exec sp_rename '[SandBox].[bob].[bob.Test2]','Test2'

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• amrinea

  SSC Eights!

  Points: 942

  More actions

  May 17, 2010 at 11:57 am

  #1168389

  Perfect Lowell. That's exactly what I was looking for. I kept trying various things, but didn't think to fully qualify it.

  Thanks,

  Adam