Can't rename a object that belongs to non-dbo schema

• SQL Guy 1

  SSCoach

  Points: 15981

  More actions

  December 31, 2013 at 1:24 pm

  #301859

  I need to rename a foreign key. It is on a table which is not dbo.

  Here is what I run:

  exec sp_rename

  @objname ='schema_name.table_name.FK_old_name',

  @newname ='FK_new_name',

  @objtype ='Object'

  I get a message :

  Msg 15248, Level 11, State 1, Procedure sp_rename, Line 321

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

  Inside of sp_rename, it selects object_id(@objname) to validate names. Where @objname is my FK_old_name. It returns null, hence it raises above error.

  If I run same code outside of sp, it returns null.

  Now, how I can get around it? Do I pass parameter for @objname properly?

  Thanks

• Lowell

  SSC Guru

  Points: 323546

  More actions

  December 31, 2013 at 1:28 pm

  #1676672

  SQL Guy 1 (12/31/2013)

  ---

  I need to rename a foreign key. It is on a table which is not dbo.

  Here is what I run:

  exec sp_rename

  @objname ='schema_name.table_name.FK_old_name',

  @newname ='FK_new_name',

  @objtype ='Object'

  I get a message :

  Msg 15248, Level 11, State 1, Procedure sp_rename, Line 321

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

  Inside of sp_rename, it selects object_id(@objname) to validate names. Where @objname is my FK_old_name. It returns null, hence it raises above error.

  If I run same code outside of sp, it returns null.

  Now, how I can get around it? Do I pass parameter for @objname properly?

  Thanks

  to rename a foreign key, you only include the name; not the schema/object.

  sp_rename 'FK_oldname','FK_newname','OBJECT'

  actually, everything except a table(constraints,pk's, defaults, etc) is the name of the object only; only a table can you references the schema as well.

  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!

• Nevyn

  SSChampion

  Points: 14210

  More actions

  December 31, 2013 at 1:32 pm

  #1676673

  Technet article on sp_rename says:

  If the object to be renamed is a constraint, object_name must be in the form schema.constraint.

  No table name

• SQL Guy 1

  SSCoach

  Points: 15981

  More actions

  December 31, 2013 at 1:47 pm

  #1676676

  Thanks both for quick reply. Big thanks to Nevin. It worked.