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.


Top Hashtags:

Advertisements

Advertisements




Advertisements