Post reply

Renaming tables in different database

  • June 2, 2010 at 4:38 am

    #235878

    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?

  • June 2, 2010 at 4:48 am

    #1175192

    Use this instead

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

    Njoi SQL'ing

    ~RD

  • June 2, 2010 at 4:52 am

    #1175194

    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

  • June 2, 2010 at 5:02 am

    #1175199

    SP_Rename only works on the current database

  • June 2, 2010 at 5:05 am

    #1175201

    ok thanks for your help

  • June 2, 2010 at 5:07 am

    #1175204

    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

  • June 2, 2010 at 5:12 am

    #1175206

    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.

  • June 3, 2010 at 5:07 am

    #1175820

    Try

    USE MyDB1

    GO

    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]

  • March 8, 2012 at 9:00 am

    #1456602

    Thanks Jan,

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

    Regards,
    Nitin

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

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

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy