TSQL: Table count per filegroup

Here’s a query that uses the SQL Server System Catalog Views to return a table count per table. I used this to check even table distribution in a data warehouse.

SELECT ds.name AS filegroup_name,
		COUNT(DISTINCT t.[object_id]) AS table_count
FROM sys.tables t
INNER JOIN sys.indexes i
	ON t.[object_id] = i.[object_id]
	AND i.is_primary_key = 1
INNER JOIN sys.filegroups ds
	ON i.data_space_id = ds.data_space_id
INNER JOIN sys.partitions p
	ON i.[object_id] = p.[object_id]
	AND i.index_id = p.index_id
GROUP BY ds.name;

The resultset will look something like…

filegroup_name table_count
file_1 5
file_2 5
file_3 5
file_4 5