Netezza / PureData – Dictionary Views
This is quick listing of Netezza dictionary view, sometimes call catalog views, which I have found useful when needing to pull lists of objects and to do other descriptive and/or investigative activities as a developer (Non-DBA):
List of User Dictionary Views
| View Type | View Name | View Description | Fields |
| User | _v_table | Returns a list of all user tables | objid, TableName, Owner, and CreateDate |
| User | _v_procedure | Returns a list of all the stored procedures and their attributes | objid, procedure, owner, createdate, objtype, description, result, numargs, arguments, proceduresignature, builtin, proceduresource, sproc, and executedasowner |
| User | _v_view | Returns a list of all user views | objid, ViewName, Owner, CreateDate, relhasindex, relkind, relchecks, reltriggers, relhasrules, relukeys, relfkeys, relhaspkey, and relnatts |
| User | _v_sequence | Returns a list of all defined sequences | objid, SeqName, Owner, and CreateDate |
| User | _v_session | Returns a list of all active sessions | ID, PID, UserName, Database, ConnectTime, ConnStatus, and LastCommand |
| User | _v_table_dist_map | Returns a list of all fields that are used to determine the table’s data distribution | objid, TableName, Owner, CreateDate, DistNum, and DistFldName |
Examples SQL’s
A listing of all User tables
Select *
from _v_table
Order by TABLENAME;
List of tables for a particular owner
Select TABLENAME, OWNER
from _v_table
WHERE OWNER = ‘<<OwnerID>>’
Order by TABLENAME;
A listing of all User procedures
Select *
from _v_procedure
Order by Procedure;
List of procedures for a particular owner
Select PROCEDURE, OWNER*
from _v_procedure
WHERE OWNER = ‘<<OwnerID>>’
Order by Procedure;
A listing of all User views
Select *
from _v_view
Order by VIEWNAME;
List of views for a particular owner
Select OBJID, VIEWNAME, OWNER
from _v_view
WHERE OWNER = ‘<<OwnerID>>’
Order by VIEWNAME;
Related References
These references provide additional information, which may be helpful, but was not covered here:
2 thoughts on “Netezza / PureData – Dictionary Views”