SQL Server - how to use "ALTER INDEX" with variables as parameters

Question

Using T-SQL, I found that I could not use "ALTER INDEX" with table / index values ​​in variables without receiving a syntax error. Is there any way to do this? I am on SQL Server 2005.

My code is as follows:

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 the ability to use variables will be useful. I assume that in one case this will use dynamic SQL, but I would really not want it if I could avoid it.

Answer 1

, , , . , , , alter , , , exec() it.

- :

declare @alter varchar(200);
set @alter = 'ALTER INDEX ' + @IDXNAME + ' ON ' + @TABLENAME + ' SET (ALLOW_PAGE_LOCKS = ON)';

exec(@alter);