SQL Server 2005

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()

ORDER BY indexstats.avg_fragmentation_in_percent desc;

 

ALTER INDEX ON REBUILD WITH (MAXDOP = 1);

dbcc show_statistics(CommandLog,PK_CommandLog);

select * from sys.dm_db_index_physical_stats(dbid, objid, indexid, partition_no, mode);

use nulls to get values for all databases etc

Â