Post reply

sp_rename error

  • 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.

  • 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.

  • 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!

  • 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

Viewing 4 posts - 1 through 3 (of 3 total)

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