Netezza Count Records from all Tables in Database and Example

  • Post author:
  • Post last modified:February 27, 2018
  • Post category:Netezza
  • Reading time:3 mins read

Let us assume you are migration data from one Netezza server to other or new higher version Netezza server. Once the migration is completed, you have to perform some mandatory checks such as availability of objects, compare count of all table between two servers.

In this article, we will discuss on Netezza Count Records from all Tables in Database and some Example.

Check:

Netezza Count Records from all Tables in Database

The first step to get count of all tables in the database is to query the system table_v_table and get all the user table which are available.

For example:

TRAINING.ADMIN(ADMIN)=> select tablename from _v_table where OBJTYPE ='TABLE' limit 10; 
 TABLENAME 
------------- 
 T1 
 T2 
 T3 
 ARRAY_T 
 PATIENT_1 
 PATIENT_2 
 MERGE_DEMO1 
 MERGE_DEMO2 
 TEST_STRING 
 STUDENTS 
(10 rows)

and then build the query with union all set operator. Remove the last union all and your count query is ready to be executed for getting count.

select 'select '||chr(40)||tablename||chr(40)||' as user_table, count(1) from '||tablename||' union all'
from _v_table 
where OBJTYPE ='TABLE';

Output:

----------------------------------------------------------- 
 select (T1( as user_table, count(1) from T1 union all 
 select (T2( as user_table, count(1) from T2 union all 
 select (T3( as user_table, count(1) from T3 union all 
 select (ARRAY_T( as user_table, count(1) from ARRAY_T union all 
 select (PATIENT_1( as user_table, count(1) from PATIENT_1 union all 
 select (PATIENT_2( as user_table, count(1) from PATIENT_2 union all 
 select (MERGE_DEMO1( as user_table, count(1) from MERGE_DEMO1 union all 
 select (MERGE_DEMO2( as user_table, count(1) from MERGE_DEMO2 union all 
 select (TEST_STRING( as user_table, count(1) from TEST_STRING union all 
 select (STUDENTS( as user_table, count(1) from STUDENTS union all 
 select (SAMPLE_TBL( as user_table, count(1) from SAMPLE_TBL union all 
 select (TEST123( as user_table, count(1) from TEST123 union all 
 select (INT_SAMPLE_SALES( as user_table, count(1) from INT_SAMPLE_SALES union all 
 select (SAMPLE_SALES( as user_table, count(1) from SAMPLE_SALES union all 
 select (TABLE_NAME( as user_table, count(1) from TABLE_NAME

Netezza Count Records from all Tables

Final step is to execute the above query and your are done.

TRAINING.ADMIN(ADMIN)=> \e 
 USER_TABLE | COUNT 
------------------+------- 
 T1 | 3 
 PATIENT_2 | 5 
 TEST123 | 0 
 SAMPLE_SALES | 7 
 TABLE_NAME | 0 
 T2 | 0 
 T3 | 6 
 ARRAY_T | 1 
 PATIENT_1 | 7 
 MERGE_DEMO1 | 7 
 MERGE_DEMO2 | 4 
 TEST_STRING | 1 
 STUDENTS | 4 
 SAMPLE_TBL | 3 
 INT_SAMPLE_SALES | 7 
(15 rows)

Netezza Count Records from all Tables in Database using nz_db_tables_rowcount

You can also use the Netezza provided nz_db_tables_rowcount script to check the count of all tables in the given database.

Samples output:

 Database TRAINING 
 Rowcount Table Name 
--------------- -------------------------------- 
 1 ARRAY_T 
 7 INT_SAMPLE_SALES 
 7 MERGE_DEMO1 
 4 MERGE_DEMO2 
 7 PATIENT_1 
 5 PATIENT_2 
 7 SAMPLE_SALES 
 3 SAMPLE_TBL 
 4 STUDENTS 
 3 TAB1 
 0 TABLE_NAME 
 0 TEST123 
 1 TEST_STRING
Tags: