Netezza / PureData – Dictionary Views

PureData Powered by Netezza, Netezza, PureData, Dictionary Views

PureData Powered by Netezza

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

  1. Pingback: Netezza/PureData – Does a Table Truncate require a groom? | Scientia Compendium

  2. Pingback: Netezza – Useful links | Scientia Compendium

Comments are closed.