Small simple scrip to automate SQL Index maintenance.

Of course, the best scripts to accomplish this is Ola Hallengren’s SQL Server Maintenance Solution, that you can find here

But if you want quick and simple, here it is;

DECLARE @IndexName varchar(255) 
DECLARE @TableName varchar(255) 
DECLARE @Frag float
DECLARE @FragCount as int
DECLARE @DbName as varchar(255)
DECLARE @DbID as int
SET @DbName = (N'MyDB');
SET @DbID = DB_ID(@DbName);

DECLARE TableCursor CURSOR FOR 
SELECT si.[name] as index_name, 
 sdm.avg_fragmentation_in_percent, 
 so.[name] as table_name ,
 sdm.fragment_count
FROM sys.dm_db_index_physical_stats (@DbID, NULL, NULL, NULL, NULL) sdm 
 inner join sys.indexes si on sdm.object_id = si.object_id and si.index_id = sdm.index_id 
 inner join sys.objects so on so.object_id = si.object_id
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @IndexName, @Frag , @TableName, @FragCount
WHILE @@FETCH_STATUS = 0 
BEGIN 
 if @Frag < 5 or @IndexName is NULL
 begin
 print '( ' + ltrim(rtrim(str(@Frag))) + '% Fragmentation, Frag. Count: ' + ltrim(rtrim(str(@FragCount))) + ' )' + ' SKIP '
 end
 else if @Frag < 30 and @Frag > 5 
 begin
 print @TableName + ' - ' + @IndexName + ' ...'
 print '( ' + ltrim(rtrim(str(@Frag))) + '% Fragmentation, Frag. Count: ' + ltrim(rtrim(str(@FragCount))) + ' )' + ' REORGANIZE '
 exec ('ALTER INDEX ' + @IndexName + ' ON [' + @TableName + '] REORGANIZE')
 end
 else if @Frag > 30
 begin
 print @TableName + ' - ' + @IndexName + ' ...'
 print '( ' + ltrim(rtrim(str(@Frag))) + '% Fragmentation, Frag. Count: ' + ltrim(rtrim(str(@FragCount))) + ' )' + ' REBUILD '
 exec ('ALTER INDEX ' + @IndexName + ' ON [' + @TableName + '] REBUILD') 
 end
 print 'Done' + char(13)
 FETCH NEXT FROM TableCursor INTO @IndexName, @Frag, @TableName, @FragCount 
 
END
CLOSE TableCursor 
DEALLOCATE TableCursor
EXEC sp_updatestats