Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

sp_rename error Expand / Collapse
Author
Message
Posted Monday, May 17, 2010 11:26 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 6:59 PM
Points: 26, Visits: 59
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.
Post #923026
Posted Monday, May 17, 2010 11:38 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 11:35 AM
Points: 31,373, Visits: 15,841
Everything is in a schema. Likely it was in the dbo schema, so change it back to that one.






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #923034
Posted Monday, May 17, 2010 11:51 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:09 AM
Points: 12,965, Visits: 32,541
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #923042
Posted Monday, May 17, 2010 11:57 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 6:59 PM
Points: 26, Visits: 59
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
Post #923045
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse