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

[#1]

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

[#2]

@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>;

command.

---

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.

[#3]

@Rohini Mathur

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

[#4]

@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

[#5]

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 

https://askdoubts.com/question/list-of-hive-internal-tables-from-metadata/#comment-20