Count the number of rows in every Table in a Database in no time!

UPDATE: There’s a better way which gives sizings too – see this post: Size every table in your database in a flash – Part 2

Here is a piece of T-SQL code that uses DMV’s (Dynamic Management Views) to give an approximate row count of every table in your database in virtually no time at all. Bear in mind it is running off collected statistics so won’t always be 100% accurate – but it’s far quicker than doing a Count(*) on a table by table basis when you just need a rough idea when doing sizing. In case you don’t know what DMV’s there are, go poke around in SSMS – [Database] > Views > System Views and see what’s there. Anyway, the code:

SELECT    s.[Name] as [Schema]
,        t.[name] as [Table]
,        SUM(p.rows) as [RowCount]

FROM sys.schemas s
LEFT JOIN sys.tables t
ON s.schema_id = t.schema_id

LEFT JOIN sys.partitions p
ON t.object_id = p.object_id

LEFT JOIN  sys.allocation_units a
ON  p.partition_id = a.container_id

WHERE    p.index_id  in(0,1) -- 0 heap table , 1 table with clustered index
AND        p.rows is not null
AND        a.type = 1  -- row-data only , not LOB

GROUP BY s.[Name], t.[name]
ORDER BY 1,2

For a very swift explanation – sys.schemas and sys.tables list the schemas and tables in the database, so joining these together on schema_id gives a list of all tables by schema in the database. Adding on sys.partitions then pulls in the partitions associated with each table, and finally sys.allocation_units pulls in the allocation units, which i’m not quite sure what they are – the guts of this query were pulled from another blog which I embarrasingly can’t trace back to now.

I’m no expert on DMV’s so if you have any views on the quality of this query – please leave a comment with your thoughts.

4 thoughts on “Count the number of rows in every Table in a Database in no time!

  1. I was in the process of composing an email about a customer’s server today where I had seen an inordinate amount of DB growth in a short time. This actually helped me isolate the specific table where the disproportionate about of data was stored.

  2. Is there a way to query objects in “another” database on the same server? The reason I ask is that I can’t build a view in the database I want to retrieve counts for due to vendor restrictions. Thanks.

  3. You should be able to using the last three parts of level naming – i.e. [SERVER].[DATABASE].[SCHEMA].[OBJECT] – assuming you have permissions. You’d need to change the following part of my sample script

    from:

    FROM sys.schemas s
    LEFT JOIN sys.tables t

    to:

    FROM [DATABASE].sys.schemas s
    LEFT JOIN [DATABASE].sys.tables t

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>