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