Keeping track of your row counts can be helpful for budgeting and capacity planning, yet accurate counts are surprisingly hard to get.



Databases keep row counts in their stats tables for planning query execution, but the results are only approximate and can be badly out of date. You can get exact counts by running a count query for every table, but this can be tedious and require external scripting.



Instead, you can get exact row counts with a single query. This query is not going to be fast: It will take as long as running select count(1) from foo on every table in your database.



Our end result will be a table of schema, table, and row counts:



<figure class="wp-block-image size-full fancybox"><img decoding="async" src="https://cdn.sisense.com/wp-content/uploads/image-01-28.png" alt="Schema, table, and row counts" class="wp-image-80823"/></figure>



<h2 class="wp-block-heading">Exact Row Counts in Postgres</h2>



To start getting our row counts, we’ll need a list of our SQL tables. We can get this easily with:



<pre class="wp-block-code"><code>select table_schema, table_name
from information_schema.tables
where 
 table_schema not in ('pg_catalog', 'information_schema') 
 and table_type='BASE TABLE'</code></pre>



From here, we need a way to turn the names of tables like &#8216;users&#8217; into an executable SQL statement. While we can trivially concatenate on &#8216;select count(1) from &#8216; to each table name, we need a way to actually run this constructed query.



Fortunately, postgres has the wondrous eval which executes strings of SQL. It’s only available from stored procedures, so we’ll write a custom function that invokes eval.



SQL Analytics Starter Kit: Best Practices, Tips, and Tricks:



<a class="action-btn " href="https://www.sisense.com/whitepapers/sql-analytics-best-practices-tips-and-tricks/" target="_blank" rel="noopener noreferrer">Get the Starter Kit</a>



We’ll want our custom function to take a table name and return the number of rows in the query. Here’s how to write a count_rows function:



<pre class="wp-block-code"><code>create or replace function 
count_rows(schema text, tablename text) returns integer
as
$body$
declare
 result integer;
 query varchar;
begin
 query := 'SELECT count(1) FROM ' || schema || '.' || tablename;
 execute query into result;
 return result;
end;
$body$
language plpgsql;</code></pre>



We can invoke this function like any other postgres function with count_rows(&#8216;users&#8217;). Adding our count_rows function to the original query will get us row counts for each table:



<pre class="wp-block-code"><code>select 
 table_schema,
 table_name, 
 count_rows(table_schema, table_name)
from information_schema.tables
where 
 table_schema not in ('pg_catalog', 'information_schema') 
 and table_type='BASE TABLE'
order by 3 desc</code></pre>



With this definition and query combined we’ll have the row count table we were aiming for.



<h2 class="wp-block-heading">MySQL</h2>



The same trick works in MySQL, but user defined functions aren’t an option for everyone. For those with this restriction, we’ll build a single query with a union per table and eval the entire string.



The query uses group_concat which packs multiple rows into a single string. This is needed to turn a list of table names into a string of many counts connected by unions.



Finally, we can execute a string as a prepared statement. This is the simplest way to run a string as a query in MySQL.



We’ll start with creating all of the select statements we want to run:



<pre class="wp-block-code"><code>select
 concat(
 'select ', 
 quote(db), ' db, ', 
 quote(tablename), ' tablename, '
 'count(1) "rowcount" ' ,
 'from ', db, '.', tablename) as sql_statements
from (
 select 
 table_schema db,
 table_name tablename
 from information_schema.tables 
 where table_schema not in 
 ('performance_schema', 'mysql', 'information_schema')
) t;</code></pre>



<figure class="wp-block-image size-large"><img decoding="async" src="https://cdn.sisense.com/wp-content/uploads/image-02-27.png" alt="SQL statements" class="wp-image-80829"/></figure>



We can expand that query to concatenate the select statements, and save the results with into @sql. Once saved, we can run it as a prepared statement.



<pre class="wp-block-code"><code>select
 -- Sort the tables by count
 concat( 
 'select * from (',
 -- Aggregate rows into a single string connected by unions
 group_concat(
 -- Build a "select count(1) from db.tablename" per table
 concat('select ', 
 quote(db), ' db, ', 
 quote(tablename), ' tablename, '
 'count(1) "rowcount" ',
 'from ', db, '.', tablename) 
 separator ' union ')
 , ') t order by 3 desc')
into @sql 
from (
 select 
 table_schema db,
 table_name tablename
 from information_schema.tables 
 where table_schema not in 
 ('performance_schema', 'mysql', 'information_schema')
) t;
-- Execute @sql
prepare s from @sql; execute s; deallocate prepare s;</code></pre>



This yields the same results as the approach we demonstrated for Postgres users.



<h2 class="wp-block-heading">Choices</h2>



We’ve given you a couple of tools to choose from for getting exact row counts. The user defined function is more readable, extensible, and reuseable. However, the group_concat approach is helpful if you do not have permission to create user defined functions, or if your database does not support them.



<h2 class="wp-block-heading">Go forth and count!</h2>



You now have a couple of ways to get exact row counts in the most common databases, and a few new SQL techniques to play with. Enjoy!



SQL Analytics Starter Kit: Best Practices, Tips, and Tricks:



<a class="action-btn " href="https://www.sisense.com/whitepapers/sql-analytics-best-practices-tips-and-tricks/" target="_blank" rel="noopener noreferrer">Get the Starter Kit</a>

Exact Row Counts for All Tables in MySQL and Postgres

SQL Superstar

LinkedIn

Twitter

GitHub

curve-image-unique-image-unique

curve

3-dark-2-image-unique-image-unique

3 DARK 2

Get the latest in analytics right in your inbox.

Article