Netezza Count All Database Objects – System Table Query

You may have to query few system tables to get information about system, for example, get list of all tables, views, system tables, synonyms, sequences, etc available in Netezza data warehouse appliance.

Getting object details information will help you to identify and manage Netezza system better. You can identify the tables or view which are needs to be deleted or removed from Netezza server.

Below are few queries that are consolidated to provide useful information:

Netezza Count All Database Objects – Tables, Views, Synonyms, System Tables, Databases

Below query provide you count of all objects available in Netezza databases. You will get object count by database names. You should have permission to query system view ‘_V_OBJ_RELATION_XDB’ to execute this statement. This query will help you to give estimates when starting new projects. You can exclude the system database when getting information.

select database::nvarchar(64) as database, OBJTYPE, count(OBJNAME) as Tables_count
from (select *
from _V_OBJ_RELATION_XDB
where database <> 'SYSTEM') a
group by database,OBJTYPE;

 DATABASE | OBJTYPE | TABLES_COUNT
----------+----------------+--------------
 TEST | TABLE | 1
 TRAINING | EXTERNAL TABLE | 1
 TRAINING | PROCEDURE | 2
 TRAINING | SEQUENCE | 1
 TRAINING | TABLE | 21
 TRAINING | VIEW | 3
(6 rows)

Netezza List All Database Objects – Tables, Views, Synonyms, System Tables, Databases

Below query provide you list of all objects available in Netezza databases. You will get object list by database names. You should have permission to query system views to execute this statement. You can exclude the system database.

select database::nvarchar(64) as database, OBJTYPE, OBJNAME
from _V_OBJ_RELATION_XDB
where database <> 'SYSTEM' ;

 DATABASE | OBJTYPE | OBJNAME
----------+----------------+------------------
 TRAINING | VIEW | PATIENT
 TRAINING | TABLE | T1
 TRAINING | TABLE | T2
 TRAINING | TABLE | T3
 TRAINING | TABLE | ARRAY_T
 TRAINING | VIEW | PATIENT_V1
...

List All Netezza Databases

Below query provide information on list of all databases available in the Netezza server. You should be an administrator or have permission to execute system view to run below query.

select distinct database::nvarchar(64) as databases
from _V_OBJ_RELATION_XDB;

 DATABASES
-----------
 SYSTEM
 TEST
 TRAINING
(3 rows)

List Netezza Users and Associated Group Names

Below query provides all users and associated group names created in Netezza server. This query will be useful to the Netezza administrators.

SELECT GROUPNAME,OWNER,USERNAME
FROM _V_GROUPUSERS;

 GROUPNAME | OWNER | USERNAME
-----------+-------+-----------
 DEVELOPER | ADMIN | TESTUSER1
 DEVELOPER | ADMIN | TESTUSER2
 PUBLIC | ADMIN | TESTUSER1
 PUBLIC | ADMIN | TESTUSER2
 PUBLIC | ADMIN | TRAINEE
(5 rows)

Read: