Error with sys.dm_db_index_physical_stats

Question

I am executing a select statement to retrieve fragmentation percentage of indexes and facing below error.

    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 SYS.DM_DB_INDEX_PHYSICAL_STATS(DB_ID(),
        A.OBJECT_ID,B.INDEX_ID,0,DEFAULT) C
    WHERE B.NAME IS NOT NULL

And Error is

Msg 413, Level 16, State 1, Line 1
Correlated parameters or sub-queries are not supported by the inline function "SYS.DM_DB_INDEX_PHYSICAL_STATS".
Msg 413, Level 16, State 1, Line 1
Correlated parameters or sub-queries are not supported by the inline function "SYS.DM_DB_INDEX_PHYSICAL_STATS".

Answer 1

If you are in 80 compatibility mode, you need:

DECLARE @dbid int = DB_ID();

SELECT t.name, i.name, ps.avg_fragmentation_in_percent 
  FROM sys.tables AS t         -- why A???
  INNER JOIN sys.indexes AS i  -- why B???
    ON t.[object_id] = i.[object_id]
  CROSS APPLY sys.dm_db_index_physical_stats
    (@dbid,NULL,NULL,0,DEFAULT) AS ps -- why C???
WHERE ps.index_id = i.index_id
AND ps.[object_id] = t.[object_id]  
AND i.name IS NOT NULL;

For 100, you need:

SELECT t.name, i.name, ps.avg_fragmentation_in_percent 
  FROM sys.tables AS t         -- why A???
  INNER JOIN sys.indexes AS i  -- why B???
    ON t.[object_id] = i.[object_id]
  CROSS APPLY sys.dm_db_index_physical_stats
    (DB_ID(),NULL,NULL,0,DEFAULT) AS ps -- why C???
WHERE ps.index_id = i.index_id
AND ps.[object_id] = t.[object_id]  
AND i.name IS NOT NULL;

The function posted in another answer looks nice and convenient, gets the right answer, and it's a clear winner when you measure just estimated cost, but be careful - there are lots of hidden costs in multi-statement TVFs when you actually go ahead and use them (and in this case you can't use an inline TVF for the same reason as the OP is getting an error message with the built-in function). Here is the cost comparison:

(And just to show the cost of individual lookups as well as no filters):

As an aside, please be careful about case (don't upper-case system objects or column names), use sensible aliases, and get in the habit of using statement terminators.

Answer 2

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