There are lot of Netezza system tables and views views available. You can get the more information about the users, tables, synonyms etc.
Read:
- Netezza data types and length restrictions
- Netezza Query History details using nz_query_history Table
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 |
Netezza System Tables and Views Examples
You can run query the above Netezza system tables and views to get required information.
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) 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)
_V_TABLE is inaccurate when it comes to RELTUPLES (rowcount). I have tried to groom and run statistics on the table but the rowcount still isn’t updated. It is only accurate when you truncate and insert.
Not sure what the intended purpose of this column is but definitely not for current row counts. I have to stick to hard coding a list of select count(*)s for each table.