Support Questions
Find answers, ask questions, and share your expertise
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

Listing only Hive internal tables


Listing only Hive internal tables


Hello All,

I am just thinking if there is any way to list only Hive internal tables/External tables? Show tables lists all but if I would like to see only internal tables, the reason behind this is - In case of housekeeping activities, If I just list the internal tables and drop them then my task is simple, other wise I will to check if the table is external/internal if I need to do a complete clean up for both metadata and raw data.





Re: Listing only Hive internal tables

Cloudera Employee

Hi Sree,


I don't think there is a way to list only internal tables using SHOW TABLES. You'll need to use DESCRIBE EXTENDED <TABLE> and look for the "tableType" property for each table.


(Alternatively, you could use different identifiers for internal and external table names, and use SHOW TABLES <pattern>, for example: SHOW TABLES 'internal_*' to list internal ones)







Re: Listing only Hive internal tables

Hello Mohit,
Thank you. I like the idea of having it in the name, which we are following
but not every one in the cluster follows, being we are one among multi

Re: Listing only Hive internal tables

New Contributor



By using hive metastore you can get the list of hive external tables and internal tables with databases names and table data location path.


Please read below links you will find query to get list of tables .


Internal Tables --



External tables ----

Don't have an account?
Coming from Hortonworks? Activate your account here