If you want to check if a table (or a column) exists avoid errors when trying to DROP or ALTER it, there's also the IF EXISTS syntax.
DROP TABLE IF EXISTS foo;
In addition, to create an alias that accepts one or more arguments, we can use bash functions.
Just type the following 4 lines in bash, with an Enter at the end.
vfind ()
{
echo "select table_name, remarks from all_tables where table_name ilike '%${1}%';" | vsql
}
Usage examples:
1) To find all tables name which include charecters “t2”, type: vfind t2
2) To find all tables name which include charecters “proj”, type: vfind proj
table_name | remarks
------------------------------------------+-----------------------------------------------------------
projections | Projection information
projection_columns | Projection columns information
projection_refreshes | Historical and current projection refreshes
projection_delete_concerns | Projections that may have delete performance concerns
rebalance_projection_status | Rebalance progress for relevant projections.
projection_recoveries | Recovery status per projection
projection_usage | Projection usage history
projection_checkpoint_epochs | Projection checkpoint epochs
projection_storage | Storage information on each Projection
deployment_projection_statements | Deployment projection statements from current DBD designs
deployment_projections | Projections to be deployed from current DBD designs
(11 rows)
3) To print a complete listing of all tables and views in your DB, type only: vfind
(Without any variables)
HyeontaeJu
Vertica Customer ✭
Answers
What's wrong with using V_CATALOG.TABLES or V_CATALOG.ALL_TABLES? Are you against using other system tables (i.e. V_CATALOG.COLUMNS)?
Other options ... You can use the vsql meta functions \d or \dt
dbadmin=> CREATE TABLE h_ju (c INT); CREATE TABLE dbadmin=> \dt h_ju List of tables Schema | Name | Kind | Owner | Comment --------+------+-------+---------+--------- public | h_ju | table | dbadmin | (1 row) dbadmin=> \dt h_ju2 No matching relations found.Or try exporting the table:
dbadmin=> SELECT export_tables('', 'h_ju'); export_tables ---------------------------------------------- CREATE TABLE public.h_ju ( c int ); (1 row) dbadmin=> SELECT export_tables('', 'h_ju2'); ERROR 2569: Catalog object h_ju2 does not existIf you want to check if a table (or a column) exists avoid errors when trying to DROP or ALTER it, there's also the IF EXISTS syntax.
DROP TABLE IF EXISTS foo;
In addition, to create an alias that accepts one or more arguments, we can use bash functions.
Just type the following 4 lines in bash, with an Enter at the end.
vfind () { echo "select table_name, remarks from all_tables where table_name ilike '%${1}%';" | vsql }Usage examples:
1) To find all tables name which include charecters “t2”, type: vfind t2
table_name | remarks ------------+--------- t22 | t2 | (2 rows)2) To find all tables name which include charecters “proj”, type: vfind proj
3) To print a complete listing of all tables and views in your DB, type only: vfind
(Without any variables)