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 8 (of 8 total)

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