SQL Query to Find All Table Names on a Database With MySQL and SQL Server Examples

How do you find names of all tables in a database is a recent  SQL interview question asked to one of my friends. There are many ways to find all table names from any database like MySQL and SQL Server. You can get table names either from INFORMATION_SCHEMA or sys.tables based upon whether you are using MySQL or SQL Server database. This is not a popular question like when to use truncate and delete or correlated vs noncorrelated subquery which you can expect almost all candidates to prepare well but this is quite common if you are working on any database like MySQL.


In this SQL tutorial, we will see examples of getting names of all tables from MySQL and the SQL Server database. In MySQL, there are two ways to find the names of all tables, either by using the "show" keyword or by query INFORMATION_SCHEMA.

In the case of SQL Server or MSSQL, You can either use sys.tables or INFORMATION_SCHEMA to get all table names for a database.

But, if you are new to the SQL world, it's better to start with comprehensive SQL courses and tutorials. That will help you to learn SQL better and quicker, and these kinds of articles will also make more sense once you have some SQL knowledge under your belt.

How to find the name of all tables in the MySQL database

Let's see an example of using INFORMATION_SCHEMA  and show keyword to find all table names from MySQL database:

mysql> SELECT table_name FROM information_schema.tables WHERE table_type = 'base table' AND table_schema='test';

+------------+

| TABLE_NAME |

+------------+

| department |

| employee   |

| role       |

| user       |

+------------+

4 rows in set (0.00 sec)

mysql> show tables;

+----------------+

| Tables_in_test |

+----------------+

| department     |

| employee       |

| role           |

| user           |

+----------------+

4 rows in set (0.00 sec)

While working in MySQL database I prefer to use show tables after selecting database e.g  use database_name. Compact and intuitive syntax of show keyword makes it easy to show all table names of selected database from just simple query. 

On the other hand, using INFORMATION_SCHEMA  is more general way and you can even pass name of database to SELECT query. By the way, if you are new in MySQL server and exploring it, you may find this list of frequently used MySQL server commands handy.

How to find the name of all tables in the SQL Server database

In last section, we have seen SQL query to show names of all tables in MySQL database and now we will see SQL query example for SQL Server database. Here is an example of getting all table names in MSSQL or SQL Server database:

USE test; //SELECT DATABASE
SELECT table_name FROM information_schema.tables WHERE table_type = 'base table'

or you can use sys.tables to get all table names from selected database as shown in following SQL query

USE test; //SELECT DATABASE
SELECT * FROM sys.tables

That's all on how to find all table names from database in MySQL and SQL Server. I will update this article if I found similar SQL query for other database like Oracle, Sybase or PostgreSQL etc. Though I haven't tried PostgreSQL but I am expecting show tables to work there. let me know if any of you guys tried to find names of all tables from current database in PostgreSQL.