SQL Query for Table Row Counts on Amazon Redshift Database
For Amazon Redshift data warehouse administrators and database developers, sometimes a SQL query is required to get number of rows in all tables of a Redshift database. When data professionals require the SQL Select query for table rows counts on current Amazon Redshift database, the SQL statement given in this Redshift tutorial can help.
Here is the SQL codes for the database query to fetch the number of table rows on Amazon Redshift data warehouse. Since Amazon Redshift is based on PostgreSQL database, developers can also search for PostgreSQL solutions for their problems about Redshift databases on the web.
select t.table_schema,
t.table_name,
isnull(i.tbl_rows,0) as rows
from svv_tables t
left join svv_table_info i
on t.table_schema = i.schema
and t.table_name = i.table
where t.table_type = 'BASE TABLE'
and t.table_schema not in('pg_catalog','information_schema')
order by
isnull(i.tbl_rows,0) desc,
t.table_name;
Here is the output of above SQL script executed on a sample Amazon Redshift database using DBeaver database manager tool.
Redshift developers can customize the SQL Select query according to their requirements and create a SQL View based on the database query for using later.
create view v_table_row_counts
as
select
current_database() dbname,
t.table_schema,
t.table_name,
isnull(i.tbl_rows,0) as rows
from svv_tables t
left join svv_table_info i
on t.table_schema = i.schema
and t.table_name = i.table
where t.table_type = 'BASE TABLE'
and t.table_schema not in('pg_catalog','information_schema')
order by
isnull(i.tbl_rows,0) desc,
t.table_schema,
t.table_name;
I hope SQL programmers developing applications on Amazon Redshift cloud data warehouse find this SQL query useful.