Renaming tables in different database

  • Hi i am running a script in an stored procedure in database 1 but want to rename a table in database 2 . the following is not working

    Running this in db1

    EXECUTE sp_rename N'Db2.dbo.Tmp_table', N'Db2.dbo.table', 'OBJECT'

    Getting this error

    Msg 290, Level 16, State 2, Procedure sp_rename, Line 276

    Invalid EXECUTE statement using object "Object", method "LockMatchID".

    Any ideas?

  • Use this instead

    sp_rename 'Db2..Tmp_table', 'Db2..table'

    Njoi SQL'ing


  • Tried that with no luck .. same error message.

    Can sp_rename used on tables out with the database you are using?

    It works fine from with the current database

  • SP_Rename only works on the current database

  • ok thanks for your help

  • no worries, a good way to see what is going on 'under the hood' of these stored procs is to run sp_helptext

    sp_helptext sp_rename

    quite often the comments at the top can be useful

  • I note point 5

    [5] If Database is part of the qualified @objname,

    then it must match the current database. The @newname parm can

    never be qualified.

  • Try

    USE MyDB1


    EXEC MyDB2..sp_rename 'MyTable', 'YourTable'

    A little knowledge is a dangerous thing (Alexander Pope)
    In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)[/url]

  • Thanks Jan,

    I had a same issue. It is working fine with your solution.


Viewing 9 posts - 1 through 8 (of 8 total)

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