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

[#1]

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

[#2]

@rajendra

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

[#3]

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.

[#4]

@rajendra

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.

[#5]

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