Netezza System Tables/ Views

Reading Time: 3 minutes

Netezza System Tables and Views

Below are the list of some commonly used system tables and views:

View Table Description
_v_sys_columns Return a list of all columns of table available in database. This is very important system view that can be used to search columns.
_v_aggregate _t_aggregate Returns a list of all defined aggregates
_v_synonym _t_synonym Returns a list of all synonyms in database
_v_objects Lists the all objects like tables, view, functions
_v_qrystat Returns a query status
_v_qryhist Returns query history
_v_database _t_database Returns a list of all databases
_v_datatype Returns a list of all system data types
_v_function Returns a list of all defined functions
_v_group _t_group Returns a list of all groups
_v_groupusers Returns a list of all users of a group
_v_index _t_index Returns a list of all user indexes
_v_operator _t_operator Returns a list of all defined operators
_v_procedure Returns a list of all the stored procedures and their attributes
_v_relation_column Returns a list of all attributes of a relation, Constraints and other informations
_v_relation_column_def Returns a list of all attributes of a relation that have defined defaults
_v_sequence Returns a list of all defined sequences
_v_session Returns a list of all active sessions
_v_table Returns a list of all user tables
_v_table_dist_map Returns a list of all fields that are used to determine the table’s data distribution
_v_table_index Returns a list of all user table indexes
_v_user Returns a list of all users
_v_usergroups Returns a list of all groups of which the user is a member
_v_view Returns a list of all user views
_v_load_status Display the information about the progress of loads that are running on the system

System table or view in Netezza that shows the number of records for each table

SELECT TABLENAME,
       OBJTYPE,
       OWNER,
       CREATEDATE,
       USED_BYTES,
       USED_BYTES/1073741824 as USED_GB, 
       RELTUPLES as "ROWS"
 FROM _V_TABLE_ONLY_STORAGE_STAT
 WHERE OBJCLASS = 4905 OR OBJCLASS = 4911
 ORDER BY TABLENAME;
Netezza System Tables and Views Examples

Example queries

SYSTEM.ADMIN(ADMIN)=> select SEQNAME,SEQNAME,OWNER from _v_sequence limit 2;
SEQNAME | SEQNAME | OWNER
-------------+-------------+-------
_S_REPL_CSN | _S_REPL_CSN | ADMIN
_S_HWID | _S_HWID | ADMIN
(2 rows)

Views

SELECT * FROM _V_SYS_VIEW; will give you the list of system views.

SELECT * FROM _V_VIEW; will also list some of the system views plus any user-defined views.

V_OBJECTS

SYSTEM.ADMIN(ADMIN)=> select * from _v_objects;
OBJID | OBJNAME | OWNER | CREATEDATE | OBJTYPE | OBJCLASS | DESCRIPTION | SCHEMA | SCHEMAID
——–+———+——-+———————+———-+———-+————-+——–+———-
213243 | SEQ1 | ADMIN | 2016-09-04 07:25:19 | SEQUENCE | 4909 | | ADMIN | 6
(1 row)

What to read next?

Netezza