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 TypeView NameView DescriptionFields
User _v_tableReturns a list of all user tablesobjid, TableName, Owner, and CreateDate
User_v_procedureReturns a list of all the stored procedures and their attributesobjid, procedure, owner, createdate, objtype, description, result, numargs, arguments, proceduresignature, builtin, proceduresource, sproc, and executedasowner
User_v_viewReturns a list of all user viewsobjid, ViewName, Owner, CreateDate, relhasindex, relkind, relchecks, reltriggers, relhasrules, relukeys, relfkeys, relhaspkey, and relnatts
User_v_sequenceReturns a list of all defined sequencesobjid, SeqName, Owner, and CreateDate
User_v_sessionReturns a list of all active sessionsID, PID, UserName, Database, ConnectTime, ConnStatus, and LastCommand
User_v_table_dist_mapReturns a list of all fields that are used to determine the table’s data distributionobjid, 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

Comments are closed.