Skip to content

Netezza – Get table and view count per database

Here is how you can find the total number of tables per database and number of columns associated to these tables as well as number of views per database and number of columns associated to the views.

Just save this script and execute:

for tab in `nzsql -At -c “select database from _v_database;”`
do

nzsql -At -d ${tab} -c “select database,’VIEW’,count(distinct name) from _V_RELATION_COLUMN where type=’VIEW’ group by database;”
nzsql -At -d ${tab} -c “select database,’TABLE’,count(distinct name) from _V_RELATION_COLUMN where type=’TABLE’ group by database;”
nzsql -At -d ${tab} -c “select database,’TABLE COLUMNS’,count(*) from _V_RELATION_COLUMN where type=’TABLE’ group by database;”
nzsql -At -d ${tab} -c “select database,’VIEW COLUMNS’,count(*) from _V_RELATION_COLUMN where type=’VIEW’ group by database;”

done