Rename Field And Add Field With Same Name In One

Question

I have a stored procedure that creates a table, inserts data into the table, and now I need to alter the table to

1) Rename an existing field to a new name
2) Add a new field with the same name as the field we just renamed

I tried this syntax however my SSMS freezes anytime I execute and I have to ctrl+alt+del to close....I feel that is unrelated to the syntax, but wanted to ask

    Create Table TMI (empName varchar(500), userID varchar(100), TV float)

CREATE NONCLUSTERED INDEX eNIndex 
ON TMI (empName ASC)

dbo.sp_RENAME '[dbo].TMI.TV' , 'OrigTV', 'COLUMN'

ALTER TABLE [dbo].TMI
ADD TV float;

Answer 1

Couple of things...

The sp_rename procedure is in the sys schema, not dbo.

Second, if you execute a stored procedure in a batch, then you must use the EXEC keyword.

Try changing your code to this:

CREATE Table TMI (empName varchar(500), userID varchar(100), TV float)

CREATE NONCLUSTERED INDEX eNIndex 
ON TMI (empName ASC)

EXEC sys.sp_rename '[dbo].TMI.TV' , 'OrigTV', 'COLUMN'

ALTER TABLE [dbo].TMI
ADD TV float;