SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software Amazon Web Services AWS Tutorials and Guides
Development resources, articles, tutorials, code samples and tools and downloads for ASP.Net, SQL Server, R Script, Windows, Windows Phone, AWS, SAP HANA and ABAP, like SAP UI5, Screen Personas, etc.




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.

Amazon Redshift database

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.

SQL query for Amazon Redshift database table row counts

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 using Amazon Redshift data warehouse find this query useful.





AWS Tutorials

AWS Tutorials

AWS










Copyright © 2004 - 2020 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems