How to list tables in MySQL database
Sep 11th, 2021,
Here are a couple of ways to list all tables in a MySQL database. Here we have included some possible ways.
1. Using SQL Query
To show all tables in the current MySQL database, you can use the following script:
SELECT table_schema,table_name FROM information_schema.tables WHERE table_type = 'BASE TABLE' AND table_schema = 'your_database’ AND table_schema not in ['information_schema','mysql', 'performance_schema','sys'] ORDER BY table_schema, table_name;
Sample results
As a result, you will get the list of all tables
as shown in the picture below:
2. List all tables with MySQL interactive shell command-line
If you Prefer using the MySQL interactive shell command line, the following command would help show full
tables.
All you need to do is connect to your database with the use your_database_name command, for example: use sakila.
After that, run the show full tables command. As can be seen, this command shows all the tables and views.
With an
expression in the where clause in the show tables command, you can filter the result to list only the tables using this statement show full tables where tables_type = ‘BASE TABLE’;
3. Display all table in MySQL database with ERBuilder Data Modeler
Finally, we'll have a look at ERBuilder Data Modeler , which can extract database data structure. In addition to display all tables as a list [shown in red in the picture below], you can also see them as an ER Diagram [shown in blue]. Furthermore, you can use the data model browser module which allows you to quickly explore and browse any table you desire [shown in green].
Furthermore, with the use of ERBuilder Data Modeler, you can also view details of the table and edit it. Simply by just double-clicking on the table name as you can see from the picture below, you will be getting table's columns, indexes, constraints, keys, triggers, description, and the SQL script.
4.4.1 Selecting All Data
The simplest form of SELECT
retrieves everything from a table:
mysql> SELECT * FROM pet;
+----------+--------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+----------+--------+---------+------+------------+------------+
| Fluffy | Harold | cat | f | 1993-02-04 | NULL |
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
| Fang | Benny | dog | m | 1990-08-27 | NULL |
| Bowser | Diane | dog | m | 1979-08-31 | 1995-07-29 |
| Chirpy | Gwen | bird | f | 1998-09-11 | NULL |
| Whistler | Gwen | bird | NULL | 1997-12-09 | NULL |
| Slim | Benny | snake | m | 1996-04-29 | NULL |
| Puffball | Diane | hamster | f | 1999-03-30 | NULL |
+----------+--------+---------+------+------------+------------+
This form of SELECT
uses *
, which is shorthand for “select all columns.” This is useful if you want
to review your entire table, for example, after you've just loaded it with your initial data set. For example, you may happen to think that the birth date for Bowser doesn't seem quite right. Consulting your original pedigree papers, you find that the correct birth year should be 1989, not 1979.
There are at least two ways to fix this:
Edit the file
pet.txt
to correct the error, then empty the table and reload it usingDELETE
andLOAD DATA
:mysql> DELETE FROM pet; mysql> LOAD DATA LOCAL INFILE 'pet.txt' INTO TABLE pet;
However, if you do this, you must also re-enter the record for Puffball.
Fix only the erroneous record with an
UPDATE
statement:mysql> UPDATE pet SET birth = '1989-08-31' WHERE name = 'Bowser';
The
UPDATE
changes only the record in question and does not require you to reload the table.
There is an exception to the principle that SELECT
*
selects all columns. If a table contains invisible columns, *
does not include them. For more information, see Invisible Columns.