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