Search a table in all databases in hive

Question

In Hive, how do we search a table by name in all databases?

I am a Teradata user. Is there any counterpart of systems tables (present in Teradata) like dbc.tables, dbc.columns which are present in HIVE?

Answer 1

You can use SQL like to search a table. Example: I want to search a table with the name starting from "Benchmark" I don't know the rest of it.

Input in HIVE CLI:

show tables like 'ben*'

Output:

+-----------------------+--+
|       tab_name        |
+-----------------------+--+
| benchmark_core_month  |
| benchmark_core_qtr    |
| benchmark_core_year   |
+-----------------------+--+
3 rows selected (0.224 seconds)

---

Or you can try below command if you are using Beeline

!tables

Note: It will work with Beeline only (JDBC client based)

More about beeline: http://blog.cloudera.com/blog/2014/02/migrating-from-hive-cli-to-beeline-a-primer/