How do I list all the column names in Netezza?

Question

Is there a query I can write to search all the column names for a particular database in Netezza?

Cloddish

Answer 1

Within the same database you can use the following query:

select *
from _v_odbc_columns1
where column_name like '%columnname%'

or a less Netezza specific query

select *
from information_schema.columns
where column_name like '%columnname%'

Fruitless

This was the best answer for me because same code worked well on MS Sql Server as well as Netezza. Huge saving of effort! – Morris

How do you list all columns for all databases in the server? – Employee

Answer 2

The important catalog views in netezza system are listed below

• _V_USER: the user view gives information about the users in the netezza system.
• _V_TABLE: the table view contains the list of tables created in the netezza performance system.
• _V_RELATION_COLUMN: the relation column system catalog view contains the columns available in a table.
• _V_TABLE_INDEX: this system catalog contains the information about the indexes created on table. netezza does not support creating indexes on a table as of now.
• _V_OBJECTS: lists the different objects like tables, view, functions etc. available in the netezza.

Example:

SELECT * 
FROM _V_RELATION_COLUMN
WHERE
    ATTNAME like '%GEO%' --SPECIFY COLUMN NAME
    AND type = 'TABLE'

Joy

Answer 3

You would access something similar to an information_schema. Column Name, %COW%', would use % as a wildcard...gathering any column that has 'COW' in the name

SELECT * 
FROM _V_SYS_COLUMNS 
WHERE 
COLUMN_NAME like '%COW%' 
AND TABLE_SCHEMA = 'DEV' 
ORDER BY TABLE_NAME 
;

Mim