How to query database table names [MySQL/Oracle/SQL Server]


Query MySQL database tables as a query

If you are wondering is there a way to query all the tables that are available on your MySQL, SQL Server or Oracle Database server just like a Select query, there is a way,

Example:
mysql> SELECT table_name as list_of_tables 
       FROM information_schema.tables 
       WHERE table_type = 'BASE TABLE';

+------------------------------------------------------+
| TABLE_NAME                                           |
+------------------------------------------------------+
| innodb_table_stats                                   |
| innodb_index_stats                                   |
| cond_instances                                       |
| error_log                                            |
| events_waits_current                                 |
| events_waits_history                                 |
| events_waits_history_long                            |
| events_waits_summary_by_host_by_event_name           |
| events_waits_summary_by_instance                     |
| events_waits_summary_by_thread_by_event_name         |
..
..
..
| replication_asynchronous_connection_failover_managed |
| replication_group_member_actions                     |
| replication_group_configuration_version              |
| server_cost                                          |
| engine_cost                                          |
| proxies_priv                                         |
| sys_config                                           |
| mytable                                              |
+------------------------------------------------------+
149 rows in set (0.01 sec)

The above query will provide you list of all available tables in all of your database schemas, if you want to use it for a specific schema, you can add an AND condition as below,

mysql> SELECT table_name as list_of_tables 
       FROM information_schema.tables 
       WHERE table_type = 'BASE TABLE' 
        AND table_schema='mydb';

+------------+
| TABLE_NAME |
+------------+
| mytable    |
| city       |
+------------+
1 row in set (0.00 sec)

You must have guessed it by now but what if you want to look for a certain table if its there or not or filter out table that starts with say "s", you can add such AND conditions.

Advertisements
Try Out Code2care Dev Tools:

Advertisements

Advertisements
Code2care is an initiative to publish and share varied knowledge in programming and technical areas gathered during day-to-day learnings and development activities.

Students and software developers can leverage this portal to find solutions to their various queries without re-inventing the wheel by referring to our easy to understand posts. Technical posts might include learnings, tutorials, trouble-shooting steps, video tutorials, code snippets, how-to, blogs, articles, etc.

🎉 We are celebrating the 10th years of Code2care! Thank you for all your support!

We strongly support Gender Equality & Diversity.