Script to list objects by file group

• Welsh Corgi

  SSC Guru

  Points: 116520

  More actions

  February 14, 2017 at 6:48 pm

  #401622

  I need a Script to list objects by file group/file.

  Does anyone know of one?

  Thanks.

  For better, quicker answers on T-SQL questions, click on the following...
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

• Eddie Wuerch

  SSChampion

  Points: 12535

  More actions

  February 14, 2017 at 9:49 pm

  #1928543

  Because each index in each object may be on a separate filegroup or partition scheme, drive your query off of sys.indexes, and join the data_space_id column to sys.filegroups.data_space_id.
  Here's one I use that also throws in rowcounts and space used:
  SELECT schema_name(o.schema_id) + '.' + o.name AS [Table], IsNull(i.name, 'HEAP') AS [Index], fg.name AS [FGName],
   sum(row_count) AS rows,
   (sum(in_row_used_page_count) * 8.0) / 1024/1024 AS [Data GB],
   (sum(lob_used_page_count) * 8.0) / 1024/1024 AS [LOB GB],
   (sum(used_page_count) * 8.0) / 1024/1024 AS [Used GB],
   (sum(reserved_page_count) * 8.0) / 1024/1024 AS [Reserved GB]
  FROM sys.dm_db_partition_stats s INNER JOIN
   sys.objects o ON s.object_id = o.object_id INNER JOIN
   sys.indexes i ON s.object_id = i.object_id AND s.index_id = i.index_id INNER JOIN
   sys.filegroups fg ON i.data_space_id = fg.data_space_id
 WHERE o.is_ms_shipped = 0
 GROUP BY o.schema_id, o.name, i.name, fg.name
 ORDER BY [FGName], [Table]
GO


  If you're using partitioning, then you'll need to LEFT JOIN sys.filegroups instead of INNER JOIN, and you'll also need to LEFT JOIN to sys.partition_schemes.data_space_id to get the partition scheme name.

  Eddie Wuerch
  MCM: SQL