SQL Query to find all table names on database in MySQL and SQL Server Examples

How do you find names of all tables in a database is a recent  SQL interview questions asked to one of my
friend. There are many ways to find all table names form 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 candidate prepare
well but this is quite common if you are working on any database e.g. MySQL. In
this SQL tutorial we will see examples of getting names of all tables from MySQL
and SQL Server database. In MySQL there are two ways to find names of all
tables, either by using "show" keyword or
 by query INFORMATION_SCHEMA. In  case of SQL Server or MSSQL, You can either
use sys.tables or INFORMATION_SCHEMA to get all
table names for a database. 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 name of all tables in MySQL
database

Let's see 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.

How to find name of all tables in 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 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.

Source:http://javarevisited.blogspot.com/2012/10/sql-query-to-find-all-table-on-database-mysql-sqlserver.html