Does Netezza have an ODBC DESCRIBE function?

Question

I would like to DESCRIBE a table held on Netezza (so can see the variable formats, distribute on clause etc). I know this is possible (natively) using

\d <tablename>

However I am using SAS to connect (via ODBC).

Is this possible using SAS 9.1.3 code?

Answer 1

I do not know about Netezza, but if you can connect to it with SAS/ACCESS then you should be able to use DBMS specific command with SQL Pass-Through.

documentation here

Answer 2

I am not fully clear with your question, but I am assuming that you want to describe the table with nzsql command. If Yes, then try this nzsql -c "\d table_name"

Answer 3

You would need to access the system views that hold the information about the tables. You can't do this in one query, but you can do it with two.

SELECT  attname "Attribute", 
        datatype "Type", 
        CASE WHEN attnotnull='t' THEN 
            'Not Null' 
        ELSE 
            'Null' 
        END "Modifier",
        coldefault "Default"
FROM _v_relation_column col_t
    cross join _v_datatype dat_t
WHERE dat_t.objid = col_t.atttypid
    AND name='<table_name>'
ORDER BY attnum
;

SELECT attname  "Distributed on hash"
FROM _v_table_dist_map
    INNER JOIN tb_nm t ON tname = tablename
WHERE tablename ='<table_name>'
;