How do i see all the tables in a mysql query?

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 using DELETE and LOAD 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.

How do I get a list of tables in SQL query?

How do I see all tables at once in SQL?

How do I get a list of table names in MySQL?

How can I see the tables in a SQL database?