Magic of "\d" in Vertica

A quick neat way to list down important and oft-needed information like names of databases, schemas, users, tables, projections etc. We can also use patterns with the '\d' to narrow down the results.

Let's see it in action:

Connect with Vertica vsql:

vsql -U dbadmin -w vtest -h 0.0.0.0 -p 5433 -d vtest Welcome to vsql, the Vertica Analytic Database interactive terminal. Type: \h or \? for help with vsql commands \g or terminate with semicolon to execute query \q to quit vtest=> vtest=> \dn List of schemas Name | Owner | Comment --------------+---------+--------- v_internal | dbadmin | v_catalog | dbadmin | v_monitor | dbadmin | public | dbadmin | TxtIndex | dbadmin | store | dbadmin | online_sales | dbadmin | mytest | mytest | (8 rows) vtest=> \dn mytest List of schemas Name | Owner | Comment --------+--------+--------- mytest | mytest | (1 row) vtest=> \dn my* List of schemas Name | Owner | Comment --------+--------+--------- mytest | mytest | (1 row) vtest=> \dn v List of schemas Name | Owner | Comment ------+-------+--------- (0 rows) vtest=> \dn *v* List of schemas Name | Owner | Comment ------------+---------+--------- v_internal | dbadmin | v_catalog | dbadmin | v_monitor | dbadmin | (3 rows) Likewise you can list down other information like: vtest=> \dj List of projections Schema | Name | Owner | Node | Comment --------------+-----------------------------+---------+------------------+--------- mytest | ptest | mytest | v_vtest_node0002 | mytest | testtab_super | mytest | | To list down views: vtest=> \dv No relations found.

If you connect with the mytest user and run:

vtest=> \dt List of tables Schema | Name | Kind | Owner | Comment --------+---------+-------+--------+--------- mytest | testtab | table | mytest | (1 row)

Following are more '\d' options from help:

Informational \d [PATTERN] describe tables (list tables if no argument is supplied) PATTERN may include system schema name, e.g. v_catalog.* \df [PATTERN] list functions \dj [PATTERN] list projections \dn [PATTERN] list schemas \dp [PATTERN] list table access privileges \ds [PATTERN] list sequences \dS [PATTERN] list system tables. PATTERN may include system schema name such as v_catalog, v_monitor, or v_internal. Example: v_catalog.a* \dt [PATTERN] list tables \dtv [PATTERN] list tables and views \dT [PATTERN] list data types \du [PATTERN] list users \dv [PATTERN] list views  

Oracle Database Consulting Services

Ready to optimize your Oracle Database for the future?