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