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

2 Comments

  1. Earle says:

    Thanks for the sharing this query. I’d recently moved one database’s audit tracking tables to another filegroup and figured I’d use this to see how thorough I had been in moving the audit tables there. I was rather surprised when it reported a count of 1.

    So I looked at your code and saw that you were actually counting primary keys, assumption being a table should always have a foreign key. Too bad my vendor didn’t think so. Rather than rewrite their scripts to add the missing primary keys, I replaced the join condition ‘i.primary_key = 1′ with’ i.index_id < 2'. Now I have a number closer to what I expected and will probably extend your script more to investigate further.

  2. Rhys says:

    Hi Earle,

    Yes, this script does assume all your tables have a PK. It’s possible non-clustered indexes may be in other filegroups too.

    Cheers,

    Rhys

Leave a Reply

Current day month ye@r *