Netezza System query

Useful Queries :

List of tables in a database. Below Query will give you list of all tables under given database.

SELECT * FROM _V_TABLE WHERE OBJTYPE='TABLE';

List of view in a database. Below query will give you list of all views under given database.

SELECT *  FROM _V_VIEW WHERE OBJTYPE = 'VIEW'

List of tables/views and columns
SELECT * FROM _V_RELATION_COLUMN  ORDER BY NAME,ATTNUM ASC;

List of User Groups

SELECT *  FROM _V_GROUP;


List of Users and their groups
SELECT * FROM _V_GROUPUSERS;

Database & Table Size
SELECT
A.DATABASE AS DATABASENAME
,(A.OBJNAME) AS TABLE_NAME
,A.OBJTYPE AS OBJECT_TYPE
,B.USED_BYTES/POW(1024,3) AS USED_SPACE_IN_GB
FROM _V_OBJ_RELATION_XDB A INNER JOIN
_V_SYS_OBJECT_STORAGE_SIZE B
ON    A.OBJID=B.TBLID
WHERE   OBJECT_TYPE IN ('TABLE')

Tables and Skew Size
SELECT TABLENAME,OBJTYPE,OWNER,CREATEDATE,USED_BYTES,SKEW FROM _V_TABLE_ONLY_STORAGE_STAT WHERE OBJCLASS = 4905 OR OBJCLASS = 4911 ORDER BY TABLENAME;

Database Size
SELECT _V_OBJ_RELATION_XDB.database  AS "DB Name",
case when sum(_V_SYS_OBJECT_DSLICE_INFO.allocated_bytes) is null then 0 else SUM(_V_SYS_OBJECT_DSLICE_INFO.allocated_bytes)/1073741824 end AS "AllocatedSpace(in GB)"
FROM _V_SYS_OBJECT_DSLICE_INFO  INNER JOIN _V_OBJ_RELATION_XDB  ON _V_OBJ_RELATION_XDB.objid = _V_SYS_OBJECT_DSLICE_INFO.tblid
GROUP BY _V_OBJ_RELATION_XDB.database
ORDER BY _V_OBJ_RELATION_XDB.database