[Index] Size, usage, and location of your indexes

Updated my old code, since we were trying to figure out what indexes needed to get moved to the secondary (index) filegroup. You can even filter based off the usage (commented out below)
Standard disclaimer applies. Select the contents of the post, then copy/paste.

Note that there can be dupes - this is by design.  If a table spans multiple files, you'll see multiple rows returned with everything identical except for the filename.

SELECT
        o.name AS Table_Name ,
        i.NAME AS Index_Name ,
--        i.type_desc,
  CASE i.type
   WHEN 0 THEN 'Heap'
   WHEN 1 THEN 'C'
   WHEN 2 THEN 'NC'
   ELSE '?' END AS [Type],

   p.partition_number,

   p.rows AS [#Records] ,
        a.total_pages * 8 / 1024 AS [Reserved(mb)] ,
        a.used_pages * 8 / 1024 AS [Used(mb)] ,
        s.user_seeks ,
        s.user_scans ,
        s.user_lookups,
        fg.name, 
        f.name,
        f.physical_name
FROM    sys.indexes AS i
        INNER JOIN sys.partitions AS p 
   ON i.object_id = p.object_id
            AND i.index_id = p.index_id
        INNER JOIN SYS.OBJECTS O 
   ON I.OBJECT_ID = O.OBJECT_ID
        INNER JOIN sys.allocation_units AS a 
   ON ( a.type = 2
    AND p.partition_id = a.container_id
               )
   OR 
    ( ( a.type = 1 OR a.type = 3)
    AND p.hobt_id = a.container_id)
        INNER JOIN SYS.DM_DB_INDEX_USAGE_STATS S 
   ON S.OBJECT_ID = I.OBJECT_ID
   AND I.INDEX_ID = S.INDEX_ID
   AND DATABASE_ID = DB_ID(DB_NAME())
   AND o.type_desc NOT IN ( 'SYSTEM_TABLE', 'INTERNAL_TABLE' ) -- No system tables!
  LEFT OUTER JOIN sys.database_files f 
   ON f.data_space_id = a.data_space_id
  LEFT OUTER JOIN sys.filegroups fg 
   ON fg.data_space_id = a.data_space_id
WHERE   OBJECTPROPERTY(O.OBJECT_ID, 'IsUserTable') = 1
        AND i.TYPE_DESC <> 'HEAP'
        AND i.type <> 1 -- clustered index
--AND (ISNULL(s.user_seeks, 0) + ISNULL(s.user_scans, 0) + ISNULL(s.user_lookups, 0)) < 100 

ORDER BY o.NAME ,
        i.name