Post reply

SP_Rename column in different database

  • June 20, 2003 at 10:25 pm

    #81076

    I need to rename a column that exists in two tables of the same name but in different databases in one stored procedure. The problem is that it does not seem that SP_RENAME will work against 2 different database contexts.

    A standard SP_REname would look like this

    sp_rename 'table.field', 'newFieldName', 'COLUMN'

    I need to immediately follow this with:

    sp_rename 'otherdb.dbo.table.field', 'newFieldName', 'Column'

    SQL server won't allow me to do it through one stored procedure.

    Any ideas of how I can get around this?

    Thanks,

    Alex Gadea

    Apptik Inc.

  • June 21, 2003 at 5:06 am

    #462512

    One workaround.

    From your sp give a call to another sp in the target database and use sp_rename there.

    /*sp in first database where column name has changed already*/ 

    

    CREATE PROCEDURE dbo.a AS

    exec northwind.dbo.a

    GO

    

    /*and in northwind put in code to change the name of column*/

    

    CREATE PROCEDURE dbo.a AS

    exec sp_rename 'Table.ColumnOldName',

        'ColumnNewName' ,

        'Column'

    GO

  • June 22, 2003 at 4:26 am

    #462532

    Another option is to use EXECUTE:

    create procedure myrenamer

    as

    execute ('use otherdb exec sp_rename ''thetable.[colname]'', ''newcolname'', ''COLUMN''')


    Cheers,
    - Mark

  • June 22, 2003 at 7:56 am

    #462535

    I actually tried both options and they both worked. I ultimately went with the secondary stored procedure option because of some other issues with our approach. Thanks for the responses!

Viewing 4 posts - 1 through 3 (of 3 total)

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