SQL Query for Rows Count of All Tables on Greenplum Database

This Greenplum tutorial shows SQL developers to return the list of all database tables with the count of rows in those database tables. Database developers sometimes require to display rows count for all database tables in a Greenplum database using a single SQL query. Programmers can find two appoaches and two sample queries in this SQL tutorial.

Create SQL Function for Rows Count

In theory, a simple way to display the rows count for all database tables is creating a function which returns the record counts for a specific table and apply this function to all database tables in the Greenplum database.

The SQL query which returns the number of rows stored in a database table is same for all data platforms. A SQL with COUNT() aggregation function with qualified name of the database table (schema name and table name) in FROM clause.


SELECT count(*) FROM public.myTable

Let's now let the SQL developers convert above SQL query into a dynamic SQL and place codes in a SQL function. For example, below SQL function returns row count for a given input table which is also identified with its schema name.


CREATE OR REPLACE FUNCTION fn_rows_count (

 name text,

 table_name text

) RETURNS integer

AS

$body$



DECLARE

 rows integer;

 sql varchar;



BEGIN



 sql := 'SELECT count(*) FROM ' || schema_name || '.' || table_name;

 EXECUTE sql INTO rows;

 RETURN rows;



END;



$body$

LANGUAGE plpgsql;

It is as simple as below to use above SQL function fn_rows_count in a SQL query


select public.fn_rows_count('poc','vbak');

The output is as follows in this example

SQL function on Greenplum database returning row count

Now let's append this SQL function in a SELECT query which returns all database tables on a specific Greenplum database.
information_schema.tables is one of the easiest system views for SQL developers to query for database tables list.


select

 table_schema,

 table_name,

 fn_rows_count(table_schema,table_name)

from information_schema.tables

where

 table_catalog = 'dev'

 and table_schema = 'poc'

 and table_type = 'BASE TABLE'

order by 3 desc;

Here is the output on a sample Greenplum database

Greenplum database tables row count SQL query

All Database Tables with Rows Count

Another SQL query which returns table row counts is querying the pg_stat_user_tables statistics table.
The n_live_tup column can provide the rows count in related database table.
In following SQL SELECT query, I also add the SQL function fn_rows_count to validate the results.


SELECT

 schemaname, relname, n_live_tup,

 fn_rows_count(schemaname, relname)

FROM pg_stat_user_tables

WHERE schemaname = 'poc'

ORDER BY n_live_tup DESC;

As seen in below results screenshot, this Greenplum SQL query also returns the same results.

SQL query for all database tables row counts

I know there are many other Greenplum database table/views or PostgreSQL tables that we can get rows count for all database tables easily. I wanted two show only two different approached in this Greenplum SQL tutorial.