June 20, 2003 at 10:25 pm
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
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
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
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
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