Search a table in all databases in hive

Searching for tables with name containing infob across all Hive databases

for i in `hive -e "show schemas"`; do echo "Hive DB: $i"; hive -e "use $i; show tables"|grep "infob"; done

@hisi's answer is elegant. However it induce an error with lacking memory for GC on our cluster. So, there is another less elegant approach that works for me.

Let foo is the table name to search. So

hadoop fs -ls -R -C /apps/hive/warehouse/ 2>/dev/null | grep '/apps/hive/warehouse/[^/]\{1,\}/foo$'

If one does not remember exact name of table but only substring bar in table name, then command is

hadoop fs -ls -R -C /apps/hive/warehouse/ 2>/dev/null | grep '/apps/hive/warehouse/[^/]\{1,\}/[^/]\{1,\}$' | grep bar

That's an extention of Mantej Singh's answer: you can use pyspark to find tables across all Hive databases (not just one):

from functools import reduce
from pyspark import SparkContext, HiveContext
from pyspark.sql import DataFrame

sc = SparkContext()
sqlContext = HiveContext(sc)

dbnames = [row.databaseName for row in sqlContext.sql('SHOW DATABASES').collect()]

tnames = []
for dbname in dbnames:
    tnames.append(sqlContext.sql('SHOW TABLES IN {} LIKE "%your_pattern%"'.format(dbname)))

tables = reduce(DataFrame.union, tnames)
tables.show()

The way to do this is to iterate through the databases searching for table with a specified name.