Wednesday, June 4, 2008

Filegroups : Where are my Indexes / Tables Stored?

Which filegroups are utilised by which database objects?
A Tsql script to help out...
SELECT
   fg.data_space_id
 , fg.name
 , OBJECT_NAME(p.object_id) AS [ObjectName]
 , p.index_id
    , dbf.name
    , dbf.physical_name
    , dbf.size*8/1024 AS [Size] -- Size of file 
    -- size is in number of 8KB pages. Multiply by 8 to get figure per extent (64KB),
    -- then divide by 1024 to get index size in MB.
 FROM sys.filegroups fg
 LEFT JOIN sys.database_files dbf
      ON fg.data_space_id = dbf.data_space_id
 LEFT JOIN sys.partitions p
      ON fg.data_space_id = p.partition_number
 WHERE (p.object_id>4096 OR p.object_id IS NULL)

No comments: