Friday, September 4, 2015

\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 -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=> \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:

\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

No comments: