Netezza Shell Script: List of materialized views

Reading Time: < 1 minute

This shell script can be resued to iterate through and all databases to run any nzsql command.

# This script will list all the materialized views in all databases

PATH=`dirname \`which $0\``:$PATH

DATABASE=""
DATABASES=""
TABLES=""

if [ "$DATABASE" = "" ]; then
        DATABASES=`nz_get_database_names`
else
        DATABASES=$DATABASE
fi

for DATABASE in $DATABASES; do

                TABLES=`nzsql -d \"${DATABASE}\" -A -t -c "select VIEWNAME from _v_view where objtype = 'MATERIALIZED VIEW';"`

if [ "$TABLES" = "" ]; then

echo "No Materialized View found in" $DATABASE

else

echo "Materialized Views in" $DATABASE
echo "---------------------------------------------------------"
echo $TABLES
echo "---------------------------------------------------------"

fi

done