When you need to check the health or status of all INDEXs and need to REBUILD the required INDEXs depends on the Fragmentation level (%), you can get help from below SQL queries.
In my example, I will show how to get the fragmented indexes which have above 20% fragmentation level.
INDEX STATUS check
------------------------------------------
SELECT dbschemas.[name] AS 'SchemaName',
dbtables.[name] AS 'TableName',
dbindexes.[name] AS 'IndexName',
ROUND(indexstats.avg_fragmentation_in_percent,2) AS avg_fragmentation_in_percent,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables
ON dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas
ON dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes
ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
AND dbindexes.type_desc = 'NONCLUSTERED'
AND dbindexes.[name] IS NOT NULL
AND indexstats.avg_fragmentation_in_percent > 20
ORDER BY
ROUND(indexstats.avg_fragmentation_in_percent,2) DESC
------------------------------------------
Now using below SQL, I will create the REBUILD command to Defragment the infected indexes...
SQL to generate Rebuild Command
------------------------------------------
SELECT
'ALTER INDEX '+dbindexes.[name]+' on '+dbschemas.[name]+'.'+ dbtables.[name]+' REBUILD;',
dbschemas.[name] AS 'SchemaName',
dbtables.[name] AS 'TableName',
dbindexes.[name] AS 'IndexName',
ROUND(indexstats.avg_fragmentation_in_percent,2) AS avg_fragmentation_in_percent,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables
ON dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas
ON dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes
ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
AND dbindexes.type_desc = 'NONCLUSTERED'
AND dbindexes.[name] IS NOT NULL
AND indexstats.avg_fragmentation_in_percent > 20
ORDER BY
ROUND(indexstats.avg_fragmentation_in_percent,2) DESC
------------------------------------------
