18
ALTER TABLE [TEMP]
SP_RENAME '[TEMP].[Day]', 'GT', 'COLUMN'

I am trying to rename Day to GT and am getting the error

Incorrect syntax near 'SP_RENAME'

SQL Server Management Studio says the error is on SP_RENAME

NOTE: I'm open to other options besides sp_rename

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Eric Francis
  • 23,039
  • 31
  • 88
  • 122

3 Answers3

43

SP_RENAME is not part of the ALTER TABLE statement. It is a system stored procedure and therefore it should be invoked using the EXEC/EXECUTE statement, like this:

exec SP_RENAME '[TEMP].[Day]', 'GT', 'COLUMN'

(without the alter table temp bit)

Andriy M
  • 76,112
  • 17
  • 94
  • 154
juergen d
  • 201,996
  • 37
  • 293
  • 362
  • 4
    I'd also add the EXEC there, in case the OP copies it into a batch and it's not the first statement. – Aaron Bertrand May 31 '12 at 20:30
  • 1
    @ericf right where juergen put it, and please don't ever say "it's not working" - what does "it's not working" mean? Do you get an error message? If so, copy and paste it so we have some clue what you mean. – Aaron Bertrand May 31 '12 at 20:34
  • [this example in sqlfiddle](http://sqlfiddle.com/#!3/00034/2) not found – Kiquenet Aug 16 '16 at 08:59
  • So just calling `sp_rename` without exec seems to work as long as it is the only statement. However when I move this statement into a conditional (to make sure the field was not already renamed) I had to add the `exec` as indicated here. – danielson317 Jan 02 '17 at 19:32
4

You need to start each SP_RENAME with an EXEC

Elie
  • 149
  • 1
  • 1
0

Worked for the answer which was quoted by @juergen d. 

PRINT N'Modifying Schema for SAMPLE_TABLE started'
DECLARE @COLNAME_DEAL_TERM VARCHAR(200) ;
SET @COLNAME_DEAL_TERM = 'SAMPLE_COL_NAME';

IF COL_LENGTH('SAMPLE', @COLNAME_DEAL_TERM ) IS NULL
BEGIN
    exec SP_RENAME 'SAMPLE.portfolio',@COLNAME_DEAL_TERM , 'COLUMN';
END;
GO
Prasanna Iyer
  • 31
  • 5