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,

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

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

Try Out Code2care Dev Tools:


#Android #Java #Linux #Microsoft #Google #Python #MacOS #Notepad++ #Microsoft Teams #CSS #PHP #SharePoint #Html #Linux #C-Program #Bootstrap #jQuery #Sublime #Android-Studio #Facebook #Eclipse #WhatsApp #News #MySQL #Json #HowTos #JavaScript #FTP #S3


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 Tos, Blogs, Articles, etc.
🎉 We are celebrating the 10th years of Code2care! Thank you for all your support! We hope we made a difference.
We strong support Gender Equality & Diversity.

We stand in solidarity with Ukraine - Make a donation to UNHCR -