
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.
More Posts related to MySQL,
- MySQL #6 - Error on delete of './my-database/db.opt' (Errcode: 13 - Permission denied)
- MySQL ERROR 1064 (42000): You have an error in your SQL syntax [fix]
- [fix] Loading class com.mysql.jdbc.Driver is deprecated
- MySQL 1005 Error : SQLSTATE: HY000 (ER_CANT_CREATE_TABLE) Message: Can't create table '%s' (errno: 150)
- MySQL: Error Code: 1175 You are using safe update mode
- MySQL Query for Schema/Structure of a Table
- MySQL : Error :1004 SQLSTATE: HY000 (ER_CANT_CREATE_FILE) Can't create file
- Connection Failed: 1130 PHP MySQL Error
- Fix mySQL Error Cant connect to local MySQL server through socket /var/run/mysqld/mysqld.sock ERROR 2002 HY000
- How to find AUTO_INCREMENT Fields value in MySQL table
- [Fix] MySQL No database selected - ERROR 1046 (3D000)
- How to clear MySQL Console Screen
- [Fix] MySQL Docker ERROR 1045 (28000): Access denied for user root@localhost (using password: YES/NO)
- Fix MySQL ERROR 1045 (28000): Access denied for user root@localhost (using password: YES)
- Display List of Databases in mysql Command Line
- MySQL Error :1007 SQLSTATE: HY000 (ER_DB_CREATE_EXISTS) Message: Can't create database '%s'; database exists
- Fix MySQL - Error: (OperationalError) (HY000) [08S01] Communications link failure
- MySQL: How to Select Database via Terminal/Command Line
- MySQL Error :1006 SQLSTATE: HY000 (ER_CANT_CREATE_DB) Message: Can't create database 'mydb' (errno: 28)
- How to query database table names [MySQL/Oracle/SQL Server]
- MySQL : Error :1000 SQLSTATE: HY000 (ER_HASHCHK) Message: hashchk Server Error
- Identify MySQL version in MySQL Command Line Prompt
More Posts:
- [Fixed] Git Clone git@github.com: Permission denied (publickey) Could not read from remote repository Fatal error - Git
- Syntax error, parameterized types are only available if source level is 1.5 or greater [Java] - Eclipse
- How to open a new tab in Notepad++ - NotepadPlusPlus
- Convert GMT/UTC timezone in Excel to EST, CST, MST, PST, AST and HST - Microsoft
- [Solution] macOS could not be installed on your computer OSInstall.mpkg appears to be missing or damaged - MacOS
- Merge multiple zip files without unzipping (extracting) - HowTos
- Only Chessboard using HTML and CSS Code Example - CSS
- How to switch to Powershell on Mac Terminal - Powershell