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!
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
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.
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))
