How to search column_names in Vertica?

Question

Anyone know of a handy function to search through column_names in Vertica? From the documentation, it seems like \d only queries table_names. I'm looking for something like MySQL's information_schema.columns, but can't find any information about a similar table of meta-data.

Thanks!

Solberg

Answer 1

In 5.1 if you have enough permissions you can do

SELECT * FROM v_catalog.columns;

to access columns's info, for some things you'll need to join with

v_catalog.tables

Moiramoirai

search in v_catalog.view_columns if you're looking for your answers in views instead of tables – Strata

Answer 2

The answer may differ depending on the version of Vertica you are using.

In the latest version, 5.1, there is a COLUMNS system table. Just from looking at the online documentation here seems to be the most useful columns with their types:

TABLE_SCHEMA VARCHAR
TABLE_NAME VARCHAR
DATA_TYPE VARCHAR

That should give you what you need. If your version doesn't have the system table, let me know what version you're running and I'll see what we can do.

Mopey

Answer 3

Wrap this python script in a shell function and you'll be able to see all tables that contain any two columns: import argparse

parser = argparse.ArgumentParser(description='Find Vertica attributes in tables')
parser.add_argument('names', metavar='N', type=str, nargs='+', help='attribute names')
args = parser.parse_args()


def vert_attributes(*names):
    first_name = names[0].lower()
    first = "select root.table_name, root.column_name from v_catalog.columns root "
    last = " where root.column_name like '%s' " % first_name
    names = names[1:]
    if len(names) >= 1:
        return first + " ".join([" inner join (select table_name from v_catalog.columns where column_name like '%s') q%s on root.table_name = q%s.table_name " % (name.lower(), index, index) for index,name in enumerate(names)]) + last
    else:
        return first + last

print nz_attributes(*tuple(args.names))

Claymore