Support Questions
Find answers, ask questions, and share your expertise

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

Solved Go to solution

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

New Contributor

'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

1 ACCEPTED SOLUTION

Accepted Solutions

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

Rising Star

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)

View solution in original post

2 REPLIES 2

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

Rising Star

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)

View solution in original post

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

hive> USE DB1; 

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