Stored Procedure for cleaning indexes for SQL Server

sp_index_maintenance.sql

CREATE PROCEDURE sp_index_maintenance
AS
    -- Drop temp table if it exists
    IF OBJECT_ID(N'tempdb..#FragmentedIndexes') IS NOT NULL
    BEGIN
    DROP TABLE #FragmentedIndexes
    END

    -- Add fragmented indexes to temp table
    CREATE TABLE #FragmentedIndexes
    (
        ID int identity (1,1),
        TableName varchar(500),
        IndexName varchar(500),
        SchemaName varchar(500),
        Fragmentation_Percentage float
    )
    INSERT INTO #FragmentedIndexes
    SELECT
        OBJECT_NAME(ps.object_id) AS TableName,
        i.name AS IndexName,
        s.name AS SchemaName,
        ips.avg_fragmentation_in_percent as Fragmentation
    FROM
        sys.dm_db_partition_stats ps
    INNER JOIN
        sys.indexes i
    ON ps.object_id = i.object_id
    AND ps.index_id = i.index_id
    INNER JOIN sys.tables t on t.object_id = ps.object_id
    INNER JOIN sys.schemas s on t.schema_id = s.schema_id
    CROSS APPLY sys.dm_db_index_physical_stats(DB_ID(), ps.object_id, ps.index_id, null, 'LIMITED') ips
    WHERE ips.avg_fragmentation_in_percent > 0
    ORDER BY ips.avg_fragmentation_in_percent DESC

    -- if you just want to view the fragmentation of the database, uncomment 
    -- this line and copy lines 3 through 38 and run as a query
    -- select * from #FragmentedIndexes

    -- Loop through and rebuild/reorganize indexes
    declare @FragmentedIndexes int
    declare @i int=0
    declare @TableName varchar(500)
    declare @IndexName varchar(500)
    declare @SchemaName varchar(500)
    declare @FragmentationPercent float
    declare @RebuildCommand nvarchar(max)

    set @FragmentedIndexes=(select count(1) from #FragmentedIndexes)
    while @i<@FragmentedIndexes
    begin
    select top 1 @TableName = TableName, @IndexName = indexName, @SchemaName = schemaName, @FragmentationPercent = Fragmentation_Percentage from #FragmentedIndexes
    if @FragmentationPercent >30
    Begin 
    set @RebuildCommand =N'ALTER INDEX ['+@IndexName + '] ON ['+@SchemaName+'].['+@TableName+'] REBUILD'
    EXECUTE sp_executesql @RebuildCommand
    End
    Else if @FragmentationPercent < 30
    Begin 
    set @RebuildCommand =N'ALTER INDEX ['+@IndexName + '] ON ['+@SchemaName+'].['+@TableName+'] REORGANIZE'
    EXECUTE sp_executesql @RebuildCommand
    End
    set @i=@i+1
    delete from #FragmentedIndexes where TableName=@TableName and indexName=@IndexName and schemaName=@SchemaName
    End
GO