index.sql

index.sql

DECLARE @tableName VARCHAR(255) = 'YourTableName';
DECLARE @sql NVARCHAR(MAX);

-- Cursor to hold index names
DECLARE index_cursor CURSOR FOR
SELECT name
FROM YourDatabaseName.sys.indexes
WHERE object_id = OBJECT_ID(@tableName) AND type > 0;

-- Loop through each index and generate drop/create statements
OPEN index_cursor;
DECLARE @indexName VARCHAR(255);

FETCH NEXT FROM index_cursor INTO @indexName;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = 'DROP INDEX ' + @indexName + ' ON ' + @tableName + ';';
    PRINT @sql;
    
    SET @sql = 'CREATE INDEX ' + @indexName + ' ON ' + @tableName + '(...);';
    PRINT '-- Define columns for ' + @indexName;
    PRINT @sql;

    FETCH NEXT FROM index_cursor INTO @indexName;
END;

-- Cleanup
CLOSE index_cursor;
DEALLOCATE index_cursor;