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)
Published on 22-Oct-2018 13:00:00
