Wednesday, August 14, 2013

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

How to find all table in a database SQL Server and MySQL exampleLet'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.

1 comment :

Anonymous said...

Nice one!!

Here is some more on Oracle--
http://techythought.wordpress.com/2012/11/10/oracle-sql-find-tables-having-a-particular-column/

Post a Comment