SQL maintenance Query

<pre class="wp-block-syntaxhighlighter-code">--Change the <YOURDATABASE> variable to the name of your EPM database that you are running the query against.

USE <YOURDATABASE>

SELECT Object_name(object_id, database_id) AS table_name,
      index_id,
      partition_number,
      avg_fragmentation_in_percent,
      fragment_count,
      page_count
FROM  sys.Dm_db_index_physical_stats(NULL, NULL, NULL, NULL, NULL)
WHERE  index_type_desc = N'CLUSTERED INDEX'
      AND avg_fragmentation_in_percent > 5
      AND page_count >= 20
ORDER  BY avg_fragmentation_in_percent desc</pre>

-- Rebuild fragmented indexes

DECLARE @TableName VARCHAR(255);
DECLARE @sql NVARCHAR(500);
DECLARE @fillfactor INT;
SET @fillfactor = 70;
DECLARE [TableCursor] CURSOR FOR
SELECT  OBJECT_SCHEMA_NAME([object_id]) + '.' + [name] AS [TableName]
FROM    [sys].[tables];
OPEN [TableCursor];
FETCH NEXT FROM [TableCursor]
INTO @TableName;
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql
        = N'ALTER INDEX ALL ON ' + @TableName + N' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3), @fillfactor)
          + N')';
    EXEC (@sql);
    FETCH NEXT FROM [TableCursor]
    INTO @TableName;
END;
CLOSE [TableCursor];
DEALLOCATE [TableCursor];
GO