Support Questions

Find answers, ask questions, and share your expertise
Check out our newest addition to the community, the Cloudera Data Analytics (CDA) group hub.

Query to access all managed tables of a given database in Hive Metastore

New Contributor

how to see metadata information for all tables available in a particular databases as DESCRIBE FORMATTED table_name is extracting information of a single table and i have 200 tables to be exected. please suggest if it is possible


Super Guru

@Rohini Mathur

Using Shell script:

one way of doing this would be using Shell script and to get all tables from the database

show tables from <db_name>;

then store all the tables into a variable and then loop through the each variable and execute

show create table <table_name>;


Using Spark:

Another way would be

Using spark.catalog.listtables("<db_name>") to list out all the tables from database then filter out only the managed tables and execute show create table on the list of managed tables..

Using Hive metastore db:

Hive stores all the table information in metastore like mysql..etc you can also get information about tables from metastore also.

Super Guru

@Rohini Mathur

Please check this and this link to get location of the hive table.

New Contributor

@Shu: Thanks but i am trying to pull information from command describe formatted demo_table to hive table, where columns are col_name|data_type|comment|Database|Owner|CreateTime|Table Parameters|COLUMN_STATS_ACCURATE|numFiles|numRows|. Please suggest is there any way to do this

New Contributor

Hello @rohinimathursjc 


The best way to get the list of Hive external tables and Managed tables is using hive metastore. yes by applying some quires on hive metastore tables we can divide both Hive external tables and Managed tables . please follow the below link to get the queries


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