[Free Space] finding what filegroups your data is saved onto.

Using this to tell me where my indexes are, but more importantly - where my data is. What filegroup/file. It produces multiple rows when there are multiple files for a filegroup; need to code a better way. Note that all the WHERE clauses are optional; we couldn't figure out where our data was until we dropped them. 10 gig in a heap table, and 20gb in a service broker table.

SELECT

o.name AS Table_Name ,

i.NAME AS Index_Name ,

CASE i.type

WHEN 0 THEN 'Heap'

WHEN 1 THEN 'C'

WHEN 2 THEN 'NC'

ELSE '?' END AS [Type],

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

--AND (ISNULL(s.user_seeks, 0) + ISNULL(s.user_scans, 0) + ISNULL(s.user_lookups, 0)) < 100

WHERE OBJECTPROPERTY(O.OBJECT_ID, 'IsUserTable') = 1

--AND i.TYPE_DESC <> 'HEAP'

AND i.type <> 1 -- clustered index

ORDER BY o.NAME ,

i.name