Error with sys.dm_db_index_physical_stats 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".
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.