EXEC sp_msforeachdb 'Use ?;INSERT INTO #Frag (DBName,TableName,SchemaName,IndexName,AvgFragment)Select db_name() ,object_name(s.object_id) As ObjectName ,object_schema_name(s.object_id) As SchemaName ,s.avg_fragmentation_in_percent From sys.dm_db_index_physical_stats(db_id(), Null, Null, Null, ''Sampled'') s Join sys.indexes i On i.object_id = s.object_id And i.index_id = s.index_id Where i.index_id > 0 And i.index_id < 255 And s.avg_fragmentation_in_percent > 20'
EXEC sp_msforeachdb 'Use ?;INSERT INTO #Frag (DBName,TableName,SchemaName,IndexName,AvgFragment)Select db_name() ,object_name(s.object_id) As ObjectName ,object_schema_name(s.object_id) As SchemaName ,i.Name As IndexName ,s.avg_fragmentation_in_percent From sys.dm_db_index_physical_stats(db_id(), Null, Null, Null, ''Sampled'') s Join sys.indexes i On i.object_id = s.object_id And i.index_id = s.index_id Where i.index_id > 0 And i.index_id < 255 And s.avg_fragmentation_in_percent > 20'
Select db_name() ,object_name(s.object_id) As ObjectName --,object_schema_name(s.object_id) As SchemaName -- 2008 ,object_name(s.object_id) As SchemaName -- 2005 ,i.Name As IndexName ,s.avg_fragmentation_in_percent