Rebuild, recreate, or reindex indexes on SQL Server tables that have fragmentation.
DECLARE @SchemaName varchar(200),
@TableName varchar(200),
@IndexName varchar(200),
@SqlCommand nvarchar(500)
DECLARE TablesAndIndexes CURSOR FOR
SELECT Schemas.Name as SchemaName,
Tables.Name as TableName,
Indexes.Name as IndexName
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED') AS Stats
inner join sys.indexes as Indexes on Stats.Index_id = Indexes.Index_ID and Stats.object_ID = Indexes.object_ID
inner join sys.tables as Tables on Indexes.Object_ID = Tables.Object_ID
inner join sys.schemas as Schemas on Tables.schema_id = Schemas.schema_id
WHERE Indexes.Name IS NOT NULL
AND avg_fragmentation_in_percent > 10.0
AND Stats.Index_id > 0
AND Page_Count > 300
OPEN TablesAndIndexes
FETCH TablesAndIndexes
INTO @SchemaName,
@TableName,
@IndexName
-- start the main processing loop.
WHILE @@Fetch_Status = 0
BEGIN
--Print
Print('Rebuilding: [' + @IndexName + '] ON [' + @SchemaName + '].[' + @TableName + ']')
--Rebuild
BEGIN TRY
SET @SqlCommand = 'ALTER INDEX [' + @IndexName + '] ON [' + @SchemaName + '].[' + @TableName + '] REBUILD'
EXEC (@SqlCommand)
END TRY
BEGIN CATCH
END CATCH
-- Get the next row.
FETCH TablesAndIndexes
INTO @SchemaName,
@TableName,
@IndexName
END
CLOSE TablesAndIndexes
DEALLOCATE TablesAndIndexes