Error with 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.