Netezza Metadata Queries

1. Query to get a list of views and thier definitions in a database:
SELECT VIEWNAME,OWNER,CREATEDATE,DEFINITION FROM _V_VIEW WHERE OBJTYPE='VIEW';
2. Query to get a list of tables in a database:
SELECT TABLENAME,OWNER,CREATEDATE FROM _V_TABLE WHERE OBJTYPE='TABLE';
3. Query to get a list of columns from a table or a view:
SELECT ATTNUM,ATTNAME FROM _V_RELATION_COLUMN WHERE NAME=UPPER('<TABLE NAME>')
ORDER BY ATTNUM ASC;
4. Query to get list of user groups on the box:
SELECT GROUPNAME,OWNER,CREATEDATE,ROWLIMIT,SESSIONTIMEOUT,
QUERYTIMEOUT,DEF_PRIORITY,MAX_PRIORITY FROM _V_GROUP;
5. Query to get list of users and the groups they are in, on the box:
SELECT GROUPNAME,OWNER,USERNAME FROM _V_GROUPUSERS;
(Does not give any LDAP users in this query)
6. Query to find the number of rows in a table without actually querying the table:
(Sometimes needed for some really huge tables of rowcount > 80 Billion)
SELECT RELNAME TABLE_NAME,
CASE
WHEN RELTUPLES < 0
THEN ((2^32) * RELREFS) + ((2^32) + RELTUPLES )
ELSE ((2^32) * RELREFS) + ( RELTUPLES )
END NUM_ROWS
FROM
_T_CLASS,
_T_OBJECT
WHERE
_T_OBJECT.OBJID=_T_CLASS.OID AND
_T_OBJECT.OBJCLASS=4905 — DISPLAY ONLY TABLES
AND RELNAME = UPPER('<TABLE NAME>');
7. Query to check if any of the SPU's are running slower than the rest:
(This actually gives the read-write speed of each SPU that is online)
SELECT HWID, BYTE_COUNT/TOTAL_MSEC
FROM
_VT_DISK_TIMING
ORDER BY 2;
8. To get the list of tables and thier skew and size:
SELECT TABLENAME,OBJTYPE,OWNER,CREATEDATE,USED_BYTES,SKEW
FROM _V_TABLE_ONLY_STORAGE_STAT
WHERE OBJCLASS = 4905 OR OBJCLASS = 4911
ORDER BY TABLENAME;