Create a function like this in your DB:
create function dm_db_index_physical_stats_tvf
(
@db_id int
,@object_id int
,@index_id int
,@partition_number int
,@mode int
)
returns @results TABLE (
[database_id] [smallint] NULL,
[object_id] [int] NULL,
[index_id] [int] NULL,
[partition_number] [int] NULL,
[index_type_desc] [nvarchar](60) NULL,
[alloc_unit_type_desc] [nvarchar](60) NULL,
[index_depth] [tinyint] NULL,
[index_level] [tinyint] NULL,
[avg_fragmentation_in_percent] [float] NULL,
[fragment_count] [bigint] NULL,
[avg_fragment_size_in_pages] [float] NULL,
[page_count] [bigint] NULL,
[avg_page_space_used_in_percent] [float] NULL,
[record_count] [bigint] NULL,
[ghost_record_count] [bigint] NULL,
[version_ghost_record_count] [bigint] NULL,
[min_record_size_in_bytes] [int] NULL,
[max_record_size_in_bytes] [int] NULL,
[avg_record_size_in_bytes] [float] NULL,
[forwarded_record_count] [bigint] NULL,
[compressed_page_count] [bigint] NULL
)
begin
insert into @results
select *
from sys.dm_db_index_physical_stats (@db_id, @object_id, @index_id ,@partition_number ,@mode )
return
end
and now run your main query:
SELECT A.NAME,B.NAME,C.avg_fragmentation_in_percent
FROM SYS.TABLES A
INNER JOIN SYS.INDEXES B ON A.OBJECT_ID=B.object_id
CROSS APPLY dm_db_index_physical_stats_tvf(DB_ID(),
A.OBJECT_ID,B.INDEX_ID,0,DEFAULT) C
WHERE B.NAME IS NOT NULL
I have tested this on my SQL 2012 environment and it works fine.
Ref: http://sqlblogcasts.com/blogs/simons/archive/2006/11/28/dmvs-and-CROSS-APPLY.aspx