Skip to content

Archive for

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

Finding SQL Server log file location

Here are couples of ways to find the location of SQL Server log file:-

1) Connect to the SQL Server instance from SSMS and then run below query in query analyzer

SELECT SERVERPROPERTY('ErrorLogFileName')
--------------------------------------------------------------
E:\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG
(1 row(s) affected)

2) Connect to SQL Server instance from SSMS and navigate to Management -> SQL Server Logs and open the current log file. Then scroll down to the beginning of the log and somewhere around 7th line from bottom which will read like “Logging SQL Server messages in file ‘E:\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG’.”

3) Open SQL Server configuration manager then navigate to SQL Server service and open the properties. You will find the log location against Startup parameter field under advanced tab.

4) Go to Windows event viewer then application log and look for event id number 17111 and you can find the log location under General details.