Skip to content

Find all the materialized views in all databases – Netezza

You can make use of this shell script to list all the materialized views in alt the Netezza databases. Just save this script as .sh and execute. It will automatically connect to all the databases and pull the list of materialized views in each database.

# This script will list all the matearilized 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 Materilized View found in" $DATABASE

else

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

fi

done