How can I check the list of MySQL tables, in a different database than we are using currently, along with table type in the result set using IN operator?

It can be done with the SHOW FULL TABLES statement. Its Syntax would be as follows −

Syntax

SHOW FULL TABLES IN db_name

Here db_name is the name of the database from which we want to see the list of tables.

Example

We are currently using the database named ‘query’ and the MySQL query below will show us the list of tables along with table type from the database named mysql.

mysql> SHOW FULL TABLES IN mysql;
+---------------------------+------------+
| Tables_in_mysql           | Table_type |
+---------------------------+------------+
| arena                     | BASE TABLE |
| arena1                    | BASE TABLE |
| columns_priv              | BASE TABLE |
| dates                     | VIEW       |
| dates1                    | VIEW       |
| db                        | BASE TABLE |
| digits                    | VIEW       |
| engine_cost               | BASE TABLE |
| event                     | BASE TABLE |
| func                      | BASE TABLE |
| general_log               | BASE TABLE |
| gtid_executed             | BASE TABLE |
| help_category             | BASE TABLE |
| help_keyword              | BASE TABLE |
| help_relation             | BASE TABLE |
| help_topic                | BASE TABLE |
| innodb_index_stats        | BASE TABLE |
| innodb_table_stats        | BASE TABLE |
| ndb_binlog_index          | BASE TABLE |
| numbers                   | VIEW       |
| plugin                    | BASE TABLE |
| proc                      | BASE TABLE |
| procs_priv                | BASE TABLE |
| proxies_priv              | BASE TABLE |
| server_cost               | BASE TABLE |
| servers                   | BASE TABLE |
| slave_master_info         | BASE TABLE |
| slave_relay_log_info      | BASE TABLE |
| slave_worker_info         | BASE TABLE |
| slow_log                  | BASE TABLE |
| tables_priv               | BASE TABLE |
| test_date                 | BASE TABLE |
| time_zone                 | BASE TABLE |
| time_zone_leap_second     | BASE TABLE |
| time_zone_name            | BASE TABLE |
| time_zone_transition      | BASE TABLE |
| time_zone_transition_type | BASE TABLE |
| user                      | BASE TABLE |
+---------------------------+------------+
38 rows in set (0.01 sec)