Custom SSMS report to show SQL Server tables per filegroup

By:   |   Comments (1)   |   Related: > SQL Server Management Studio


At a project we have multiple filegroups created in our SQL Server data warehouse. For example, there was a filegroup to store the fact tables on, a filegroup for the dimensions, a filegroup for heavy indexes and so on. This works great at first, but after a while when people come and go on the project, tables are sometimes created and put in an incorrect filegroup or just created on the default filegroup (most likely PRIMARY). Is it possible to get an overview of all the tables and indexes to see which filegroup they are stored?


This tip will continue the series of Administration Intelligence and will provide the answer in the format of a custom report you can use in your own environment. First of all we need to construct a query that will provide us with all the information we need. The query is a modified version of a query submitted by Olaf Helper at the Microsoft Gallery.

  FileGroupName =
 ,FileGroupType = CASE DS.[type]
      WHEN 'FG' THEN 'Filegroup'
      WHEN 'FD' THEN 'Filestream'
      WHEN 'FX' THEN 'Memory-optimized'
      WHEN 'PS' THEN 'Partition Scheme'
      ELSE 'Unknown'
-- ,AllocationDesc = AU.type_desc
 ,TotalSizeKB = SUM(AU.total_pages / 0.128) -- 128 pages per megabyte
 ,UsedSizeKB  = SUM(AU.used_pages / 0.128)
 ,DataSizeKB  = SUM(AU.data_pages / 0.128)
 ,SchemaName  =
 ,TableName  =
 ,IndexType  = CASE IDX.[type]
      WHEN 0 THEN 'Heap'
      WHEN 1 THEN 'Clustered'
      WHEN 2 THEN 'Nonclustered'
      WHEN 3 THEN 'XML'
      WHEN 4 THEN 'Spatial'
      WHEN 5 THEN 'Clustered columnstore'
      WHEN 6 THEN 'Nonclustered columnstore'
      WHEN 7 THEN 'Nonclustered hash'
 ,IndexName  =
 ,is_default  = CONVERT(INT,DS.is_default)
 ,is_read_only = CONVERT(INT,DS.is_read_only)
FROM  sys.filegroups   DS -- you could also use sys.data_spaces
LEFT JOIN sys.allocation_units AU  ON DS.data_space_id = AU.data_space_id
LEFT JOIN sys.partitions   PA  ON  (AU.[type] IN (1, 3) AND AU.container_id = PA.hobt_id) 
            (AU.[type] = 2   AND AU.container_id = PA.[partition_id]) 
LEFT JOIN sys.objects    OBJ ON PA.[object_id] = OBJ.[object_id] 
LEFT JOIN sys.schemas    SCH ON OBJ.[schema_id] = SCH.[schema_id] 
LEFT JOIN sys.indexes    IDX ON  PA.[object_id] = IDX.[object_id] 
           AND PA.index_id  = IDX.index_id
WHERE  OBJ.type_desc = 'USER_TABLE' -- only include user tables
  OR DS.[type]  = 'FD'   -- or the filestream filegroup
GROUP BY,,,IDX.[type],,DS.[type],DS.is_default,DS.is_read_only -- discard different allocation units

The query returns all the filegroups of the current database – including filestream and memory-optimized filegroups - together with the various user tables, schema, index information and the usage data in kilobytes.

Custom SQL Server Reporting Services Report

The report itself first gives an overview of all the different filegroups in the database, along with their type and the number of tables. There is also a column to indicate if the filegroup is a default filegroup or not and an indicator is displayed when a filegroup is read-only.

Overview of different filegroups in the database

This tablix uses the detail information at the index level provided by the query. This means data has to be aggregated to the filegroup level. This is done by grouping on FilegroupName and FilegroupType in the details row group.

Aggregating to the filegroup level

The Boolean columns is_default and is_read_only are converted to the int data type in the query so they can be aggregated. An expression checks if the result is bigger than 0 or not. For example:

  iif(sum(Fields!is_default.Value) > 0,"Yes","No")

The number of tables is visualized through data bars. The values are derived using a distinct count on the table names. To make sure the biggest number doesn’t take all the horizontal space, the maximum value of the horizontal axis is set to the distinct count of all tables (as opposed to the distinct count of tables in the group). This is done by setting the scope of the distinct count to the entire tablix.

Configuring the horizontal axis

For example, the largest filegroup of 50 tables – with a total of 100 tables in the database – will take up 50% of a data bar.

Through an expression, the data bar of the PRIMARY filegroup gets another color so that it stands out against the other filegroups. The reason for this is that you can focus on the number of user tables that are still in the PRIMARY filegroup. It’s considered a good idea to make another filegroup the default.

Conditional formatting on data bars

The report contains another tablix with a detailed overview per filegroup.

Detailed overview of tables and indexes per filegroup

The data you see here is based on the 2014 version of the AdventureWorksDW sample database. In order to have more than one filegroup, I enabled filestream on the DimProduct table, I added some memory-optimized tables and for fun I also added full-text search. The table displays the storage used by the index or heap in kilobytes. After an index name, the type of index is added between brackets.

The final report looks like this when added to SSMS:

The custom report in SSMS

See the tip Adding Custom Reports to SQL Server Management Studio on how to add a report to SSMS.

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Koen Verbeeck Koen Verbeeck is a seasoned business intelligence consultant at AE. He has over a decade of experience with the Microsoft Data Platform in numerous industries. He holds several certifications and is a prolific writer contributing content about SSIS, ADF, SSAS, SSRS, MDS, Power BI, Snowflake and Azure services. He has spoken at PASS, SQLBits, dataMinds Connect and delivers webinars on Koen has been awarded the Microsoft MVP data platform award for many years.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Monday, July 20, 2015 - 1:01:33 PM - Ernie Back To Top (38260)


I downloaded the report and ran it against one of my databases that has over 7000 tables in 14 filegroups.  The top part of the report showed the summary on the Filegroup names and the number of tables per filegroup. 

The lower part of the report only showed the first 21 tables in the first Filegroup and nothing more.  I ran it again against a small database with only the Primary file group and 56 tables.  The bottom part of the report only showed the first 33 tables in the filegroup.

I am using SQL Server 2012.  What do I need to do to show all of the tables?



get free sql tips
agree to terms