Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SELECT dbschemas.[name] as 'Schema',
- dbtables.[name] as 'Table',
- dbindexes.[name] as 'Index',
- indexstats.avg_fragmentation_in_percent,
- indexstats.page_count,
- 'ALTER INDEX ' + dbindexes.[name] + ' ON ' + dbtables.[name] +' REBUILD;'
- 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
- avg_fragmentation_in_percent>10
- ORDER BY indexstats.avg_fragmentation_in_percent desc
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement