Using T-SQL I've found that I can't use 'ALTER INDEX' with the table/index values in variables without getting a syntax error. Is there some way this could be done ? I'm on SQL Server 2005.
My code looks like this :
DECLARE @TABLENAME VARCHAR(256)
DECLARE @IDXNAME VARCHAR(256)
DECLARE @SCHEMAID INT
SET @TABLENAME = 'T1'
SET @IDXNAME = 'T1_IDX0'
-- The next line is OK as it hardcodes the variable names
ALTER INDEX T1_IDX0 ON T1 SET (ALLOW_PAGE_LOCKS = ON)
-- The next line generates a syntax error
ALTER INDEX @IDXNAME ON @TABLENAME SET (ALLOW_PAGE_LOCKS = ON)
The syntax error looks like this :
Msg 102, Level 15, State 1, Line 7
Incorrect syntax near '@IDXNAME'.
Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'SET'.
The real code I'm working on is more complex than the above and being able to use variables would be useful. I guess one way around it would be use dynamic SQL but I'd really rather not if I could avoid it.