MySQL is a popular relational data that stores information in tables. While using tables we generally need to check and list existing tables. We can use different ways to list tables. In this tutorial, we will learn how to use show tables and Select table_name in order to list and print table names.
Connect MySQL Interactive Shell
We need to connect or run commands in MySQL to list table names. We will connect to the interactive MySQL server to run the given commands. We will use mysql command to connect local or remote MySQL interactive shell.
$ mysql -u root -p
Select MySQL Database
In order to run given SQL commands and queries, we need to specify the database we want to work on. We will use show databases and use DATABASENAME command. In the following example, we will select the database named mysql which is a default database provided by the MySQL database server.
> use mysql;
show tables SQL Query
Now we will run or show tables query in this example. We can also use SHOW TABLES which is the uppercase version and have the same effect as the lowercase version. Do not skip to add a semicolon to the end of the command.
> show tables;
From the output we can see that a lot of tables exist for the given database like columns_priv, db, engine_cost, event etc.
Show All Tables of MySQL Server with “Select table_name” Query
We have another alternative to show tables query. This will list all tables created in connected MySQL instance.
> SELECT table_name FROM information_schema.tables;
Show Tables Of Given Database with “Select table_name” Query
In this exmaple we will use Select table_name query but we will filter for given database by using where statement for table_name column. We will only list database named mysql in this example.
> SELECT table_name FROM information_schema.tables where table_schema='mysql';
Check MySQL Performance and Benchmark
MySQL table creation is first steps for database management. As a talented database administrator you should also check the MySQL performance and benchmark. The following post provides information about how to check and benchmark MySQL database server performance.