Check if table exist without using “select from” in MySQL?

We can achieve this with the help of SHOW command. Firstly, I will use my database with the help of USE command −

mysql> USE business;
Database changed

We are in the “business” database now. After that, we can check that how many tables are available for this database. The query is as follows −

mysql> SHOW tables;

The following is the output

+------------------------+
| Tables_in_business     |
+------------------------+
| addcolumntable         |
| autoincrement          |
| autoincrementtable     |
| bookindexes            |
| chardemo               |
| clonestudent           | 
| columnvaluenulldemo    |
| dateadddemo            |
| deletedemo             |
| deleterecord           |
| demo                   |
| demo1                  |
| demoascii              |
| demoauto               |
| demobcrypt             |
| demoemptyandnull       |
| demoint                |
| demoonreplace          |
| demoschema             |
| distinctdemo           |
| duplicatebookindexes   |
| duplicatefound         |
| employeetable          |
| existsrowdemo          |
| findandreplacedemo     |
| firsttable             |
| foreigntable           |
| foreigntabledemo       |
| groupdemo              |
| groupdemo1             |
| incasesensdemo         |
| int1demo               |
| intdemo                |
| latandlangdemo         |
| limitoffsetdemo        |
| milliseconddemo        |
| modifycolumnnamedemo   |
| modifydatatype         |
| moneydemo              |
| moviecollection        |
| mytable                |
| nonasciidemo           |
| nthrecorddemo          |
| nulldemo               |
| nullwithselect         |
| pasthistory            |
| presenthistory         |
| primarytable           |
| primarytable1          |
| primarytabledemo       |
| rownumberdemo          |
| rowstranspose          |
| rowstransposedemo      |
| secondtable            |
| sequencedemo           |
| smallintdemo           |
| spacecolumn            |
| student                |
| tblfirst               |
| tblstudent             |
| tbluni                 |
| textdemo               |
| texturl                |
| trailingandleadingdemo |
| transcationdemo        |
| unsigneddemo           |
| updtable               |
| varchardemo            |
| varchardemo1           |
| varchardemo2           |
| varcharurl             |
| whereconditon          |
+------------------------+
72 rows in set (0.03 sec)

Alternate ways by which we can check whether a table exist or not without using SELECT statement is shown below.

The syntax is as follows −

SHOW tables like ‘yourTableName’;

Now, I am applying the above query to check whether the table exist or not for my database “business”.

The query is as follows −

mysql> SHOW tables like 'tblstudent';

The following is the output

+---------------------------------+
| Tables_in_business (tblstudent) |
+---------------------------------+
| tblstudent                      |
+---------------------------------+
1 row in set (0.00 sec)

Look at the output above, the table ‘tblstudent’ is visible in my database. The case when table does not exist is as follows −

mysql> SHOW tables like 'sampledemo';
Empty set (0.00 sec)

George John

Published on 22-Oct-2018 09:30:00

• Related Questions & Answers
• MySQL SELECT from table A that does not exist in table B using JOINS?
• Different methods to check if a MySQL table exist?
• Select from table where value does not exist with MySQL?
• How to check if a column exist in a MySQL table?
• What is “SELECT TRUE” in MySQL?
• How to select from MySQL table A that does not exist in table B?
• Using combination of “AND” and “OR” in SELECT in ABAP
• Create a table named “select” in SQL databases?
• Fix error in MySQL “select ClientId,ClientName,ClientAge, from tablename”
• C++ Program to Print “Even” or “Odd” without using conditional statement
• C Program to print “Even” or “Odd” without using Conditional statement
• MySQL: delete all rows containing string “foo” in sample table “bar”?
• Can I use two where clauses like “SELECT * FROM table WHERE condition1 and condition2” in MySQL?
• Using “not equal” in MySQL?
• MySQL query to check if multiple rows exist?