Support Questions

Find answers, ask questions, and share your expertise

How to get total number of hive tables in a database.


Is it possible to get total number of hive tables? If its yes, please suggest me how to do.


Super Collaborator

use the following to compute the total # of tables you have in Hive.

hive -e "show databases" >db3.out;cat db3.out | while read line; do hive -e "use $line;show tables" >> tables3.out;done; cat tables3.out | wc -l

New Contributor

Approach is good but it also captures unwanted characters like "+------------+" and etc. and after manual cleaning of unwanted symbols from db3.out it also counts the unwanted lines from tables3.out.


you can use the below sql statement in the metastore database.

mysql -u root -e "use hive;SELECT NAME, TBL_NAME FROM DBS as a, TBLS as b where a.DB_ID=b.DB_ID;"> tables.txt

tables.txt will have the list of all tables.


If you are using the Postgres DB then use the below query to get table information :


1. log in to DB server and execute the below query :


./psql -d hive -c "SELECT \"NAME\", \"TBL_NAME\" FROM \"DBS\" as a, \"TBLS\" as b where a.\"DB_ID\"=b.\"DB_ID\";" > /tmp/tables1.txt

 > awk '{print $1" " $3}' tables1.txt >> tables.txt
 > cat /tmp/tables1.txt | wc -l 




Take a Tour of the Community
Don't have an account?
Your experience may be limited. Sign in to explore more.