Skip to content

Posts from the ‘Script’ Category

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

Find list of views built on a specific table – Netezza

You can run below query in your database to find out all the views which are built on a specific table in a specific database.

select VIEWNAME from _v_view where DEFINITION like ‘%Table Name%’ and DEFINITION like ‘%Database Name%’;

Replace Table Name with actual table name and Database Name with actual database in which the table resides.