OPCIÓN 1:
select t4.name as [schema_name]
select t4.name as [schema_name]
, t3.name as table_name
, t2.name as index_name
, t1.object_id
, t1.index_id
, t1.partition_number
, t1.index_type_desc
, t1.avg_fragmentation_in_percent
, t1.avg_fragment_size_in_pages
, t1.page_count
from sys.dm_db_index_physical_stats(db_id(),NULL,NULL,NULL,'LIMITED' ) t1
inner join sys.objects t3 on (t1.object_id = t3.object_id)
inner join sys.schemas t4 on (t3.schema_id = t4.schema_id)
inner join sys.indexes t2 on (t1.object_id = t2.object_id and t1.index_id = t2.index_id )
where
index_type_desc <> 'HEAP'
and
avg_fragmentation_in_percent >30
order by t4.name,t3.name,t2.name,partition_number
OPCIÓN 2:
OPCIÓN 2:
SELECT dbschemas.[name] as 'Schema',
dbtables.[name] as 'Table',
dbindexes.[name] as 'Index',
indexstats.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 indexstats.avg_fragmentation_in_percent >30
ORDER BY indexstats.avg_fragmentation_in_percent desc
ORDER BY indexstats.avg_fragmentation_in_percent desc
Nota: Sustituir el avg_fragmentation_in_percent por el valor que queramos buscar.
