question How to see a table belong to which database? or what all tables exists in a perticular database? in Support Questions

How to see a table belong to which database? or what all tables exists in a perticular database?

kuldeep_dwh — Fri, 16 Sep 2022 11:10:39 GMT

'Show tables' displays all the tables within Hive. Show databases displays all the database name.

How can I see database and tables exists inside same database?

e.g.

Database, table

db1, table1

db1,table2

db2,table1

Re: How to see a table belong to which database? or what all tables exists in a perticular database?

awoolford — Wed, 01 Mar 2017 13:01:50 GMT

If you have permissions to query the Hive metastore database directly, you could:

[root@hadoop01 ~]# mysql -u hive -p
Enter password: 
mysql> USE hive;
Database changed
mysql> SELECT
    ->     name AS db_name,
    ->     tbl_name
    -> FROM TBLS
    -> INNER JOIN DBS
    -> ON TBLS.DB_ID = DBS.DB_ID;
+----------+-------------------+
| db_name  | tbl_name          |
+----------+-------------------+
| medicaid | physician_compare |
| medicaid | sample            |
| medicaid | sample_orc        |
+----------+-------------------+
3 rows in set (0.00 sec)

Re: How to see a table belong to which database? or what all tables exists in a perticular database?

namaheshwari — Wed, 01 Mar 2017 13:06:54 GMT

hive> USE DB1;

Now, commands such as SHOW TABLES, will list the tables in this database DB1