Information Schema (INFORMATION_SCHEMA ) for Netezza

Question

I have been using INFORMATION_SCHEMA.COLUMNS for MS SQL for long time and has been one of my best friend. My company is changing its platoform to Netezza. Is there something relevent in Netezza where I can query and find out the Columns/Tables in Netezza just by query.

Thank You

Answer 1

You can make use of the user and system views to access this information with SQL like so:

select * from _v_table where objtype = 'TABLE';
  OBJID  | TABLENAME | OWNER |     CREATEDATE      | OBJTYPE | OBJCLASS | DESCRIPTION | RELHASINDEX | RELKIND | RELCHECKS | RELTRIGGERS | RELHASRULES | RELUKEYS | RELFKEYS | RELREFS | RELHASPKEY | RELNATTS | RELDISTMETHOD | OBJDELIM | RELTUPLES | BASEOBJID | VISIBLEID | RELVERSION | RELORIGOID | DATABASE |  OBJDB  | SCHEMA | SCHEMAID
---------+-----------+-------+---------------------+---------+----------+-------------+-------------+---------+-----------+-------------+-------------+----------+----------+---------+------------+----------+---------------+----------+-----------+-----------+-----------+------------+------------+----------+---------+--------+----------
 1249610 | TABLE_A   | ADMIN | 2015-04-13 19:33:48 | TABLE   |     4905 |             | f           | r       |         0 |           0 | f           |        0 |        0 |       0 | f          |        2 |          6005 | f        |         0 |   1249610 |   1249610 |          0 |    1249610 | MY_TEST  | 1249609 | SCOTT  |  1249608
(1 row)



select * from _v_relation_column where name = 'TABLE_A';
      OBJID  |  NAME   | OWNER |     CREATEDATE      | TYPE  | DATABASE | ATTNUM | ATTNAME |  COLID  | DESCRIPTION |      FORMAT_TYPE      | ATTNOTNULL | ATTTYPID | ATTTYPMOD | ATTLEN | ATTDISPERSION | COLDEFAULT | ATTCOLLENG | ATTDELIM | ZMAPPED | OBJCLASS | RELRESTOREDOID | OBJDELIM | ATTVERSTATUS | ATTORIGOID | ATTRESTOREDOID |  OBJDB  | SCHEMA | SCHEMAID
    ---------+---------+-------+---------------------+-------+----------+--------+---------+---------+-------------+-----------------------+------------+----------+-----------+--------+---------------+------------+------------+----------+---------+----------+----------------+----------+--------------+------------+----------------+---------+--------+----------
     1249610 | TABLE_A | ADMIN | 2015-04-13 19:33:48 | TABLE | MY_TEST  |      1 | COL1    | 1596671 |             | BIGINT                | f          |       20 |        -1 |      8 |             0 |            |          8 | f        | t       |     4905 |              0 | f        |            0 |          0 |              0 | 1249609 | SCOTT  |  1249608
     1249610 | TABLE_A | ADMIN | 2015-04-13 19:33:48 | TABLE | MY_TEST  |      2 | COL2    | 1596672 |             | CHARACTER VARYING(20) | f          |     1043 |        32 |     -1 |             0 |            |         20 | f        | f       |     4905 |              0 | f        |            0 |          0 |              0 | 1249609 | SCOTT  |  1249608
    (2 rows)

These views and others are documented here.