Renaming a Column in a Temp Table in SQL Server 2005 - Yes, You Can!

For whatever weird reason, you may need to create a temp table in one step, and then rename one of the columns. Perhaps you're creating the temp table in one stored procedure, and modifying it in another. I'm not going to tell you that's a silly thing to do, or you should just create it with the right names in the first place - I saw those kind of responses online, and it's very unhelpful! Sometimes you just need to do this type of thing.

It's not a straightforward thing to do, though. When you just run the below script that calls sp_rename:

if object_id('tempdb..#Test123') is not null drop table #Test123
create table #Test123 (field1 int)
exec sp_rename '#Test123.Field1', 'Field2', 'COLUMN'

...you get this error: "Either the parameter @objname is ambiguous or the claimed @objtype (COLUMN) is wrong."

The key is to call sp_rename from the tempdb, like so:

if object_id('tempdb..#Test123') is not null drop table #Test123
create table #Test123 (field1 int)
exec tempdb..sp_rename '#Test123.Field1', 'Field2', 'COLUMN'
select * from #Test123

And - success! Note that you'll still have problems referencing the specific fieldname that was renamed. I solved this by doing a select into another temp table. Also, the procedure you're doing this in will probably have to do a recompile - that wasn't a problem for me, either. Overall, it was a better solution than the alternatives.