CODEWITHSUNDEEP
sql-server
Russ
Russ

Reputation: 435

Vary index name with ALTER INDEX

I'm attempting to create a stored procedure to maintain various tables' indexes. I am able to retrieve the index names but am unable to use that name with the Alter Index statement:

DECLARE reorgCursor CURSOR LOCAL FAST_FORWARD FOR
SELECT a.index_id, b.name, avg_fragmentation_in_percent  
FROM sys.dm_db_index_physical_stats (DB_ID(@MyDB), OBJECT_ID(@tableName), NULL, NULL, NULL) AS a  
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id
WHERE 5 < avg_fragmentation_in_percent AND avg_fragmentation_in_percent <= 30

OPEN reorgCursor
FETCH NEXT FROM reorgCursor
INTO @indexId, @indexName, @fragPct

WHILE @@FETCH_STATUS = 0
BEGIN
    ALTER INDEX @indexName ON @tableName  
    REORGANIZE

    FETCH NEXT FROM reorgCursor
    INTO @indexId, @indexName, @fragPct
END

CLOSE reorgCursor
DEALLOCATE reorgCursor

The problem is that I get the error:

Incorrect syntax near '@indexName'. Expecting ALL, ID, or QUOTED_ID.

How might I parameterize the index name?

Upvotes: 1

Views: 417

Answers (2)

Jason W
Jason W

Reputation: 13179

To solve your error, you'll need to use dynamic SQL to build and run your query.

DECLARE @Sql NVARCHAR(5000) = N'ALTER INDEX ' + @indexName + N' ON ' + @tableName + N' REORGANIZE';
EXEC sp_executesql @Sql

However, I would recommend researching scripts others have written that are well tested and used by many already out there. They handle more scenarios you may not have run into yet (tables in non default schemas, when to reorganize vs rebuild`, etc).

One to get started: https://blogs.msdn.microsoft.com/joaol/2008/01/28/script-to-appropriate-rebuildreorganize-database-indexes-sql-server-2005/

Upvotes: 1

Pரதீப்
Pரதீப்

Reputation: 93724

You need dynamic sql 

set @indexName = Quotename(@indexName)
set @tableName = Quotename(@tableName)

exec('ALTER INDEX +'@indexName'+ ON '+@tableName+' REORGANIZE')

For database maintenance tasks like Index rebuilding or Reorganising you can use https://ola.hallengren.com/ script which takes care of most of the maintenance activites

Upvotes: 1

Related Questions