Jeffrey Williams (12/9/2008)
Jerry Hung (12/9/2008)
It will work for SQL 2005 as well if we change
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
Jerry - object_schema_name is available in SQL Server 2005 and above. Not sure why you are repeating the object_name as the schema_name for the object in the above.
Interesting
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
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
On 2005 9.0.2047 (SP1), 1399 (RTM)
'object_schema_name' is not a recognized built-in function name.
2005 9.0.3042 (SP2) supports object_schema_name
Note, above code doesn't run in compatibility 8.0 mode either clearly
'object_schema_name' is not a recognized built-in function name.