How to display all tables in MySQL with InnoDB storage engine?

MySQLMySQLi Database

To display all table names with ENGINE as InnoDB, implement the following query

mysql> SELECT TABLE_SCHEMA as DATABASENAME ,TABLE_NAME as AllTABLENAME ,ENGINE as ENGINETYPE FROM information_schema.TABLES
   -> WHERE ENGINE = 'innoDB'
   -> AND TABLE_SCHEMA NOT IN('mysql','information_schema','performance_schema');

The following is the output.

+--------------+------------------------------------------------------------------+------------+
| DATABASENAME | AllTABLENAME                                                     | ENGINETYPE |
+--------------+------------------------------------------------------------------+------------+
| sys          | sys_config                                                       | InnoDB     |
| business     | mytable                                                          | InnoDB     |
| business     | primarytable                                                     | InnoDB     |
| business     | tblstudent                                                       | InnoDB     |
| business     | modifydatatype                                                   | InnoDB     |
| business     | nulldemo                                                         | InnoDB     |
| business     | groupdemo                                                        | InnoDB     |
| sample       | mytable                                                          | InnoDB     |
| sample       | yourtable                                                        | InnoDB     |
| business     | texturl                                                          | InnoDB     |
| business     | varcharurl                                                       | InnoDB     |
| business     | addcolumntable                                                   | InnoDB     |
| business     | demoschema                                                       | InnoDB     |
| business     | demoint                                                          | InnoDB     |
| business     | nthrecorddemo                                                    | InnoDB     |
| business     | int1demo                                                         | InnoDB     |
| business     | intdemo                                                          | InnoDB     |
| business     | textdemo                                                         | InnoDB     |
| business     | chardemo                                                         | InnoDB     |
| business     | varchardemo                                                      | InnoDB     |
| business     | demo                                                             | InnoDB     |
| business     | latandlangdemo                                                   | InnoDB     |
| business     | smallintdemo                                                     | InnoDB     |
| business     | student                                                          | InnoDB     |
| business     | moviecollection                                                  | InnoDB     |
| business     | bookindexes                                                      | InnoDB     |
| business     | duplicatebookindexes                                             | InnoDB     |
| business     | tbluni                                                           | InnoDB     |
| business     | existsrowdemo                                                    | InnoDB     |
| business     | demoascii                                                        | InnoDB     |
| business     | modifycolumnnamedemo                                             | InnoDB     |
| business     | demobcrypt                                                       | InnoDB     |
| business     | primarytable1                                                    | InnoDB     |
| business     | foreigntable                                                     | InnoDB     |
| business     | autoincrementtable                                               | InnoDB     |
| business     | demoauto                                                         | InnoDB     |
| business     | demoonreplace                                                    | InnoDB     |
| business     | employeetable                                                    | InnoDB     |
| business     | whereconditon                                                    | InnoDB     |
| business     | moneydemo                                                        | InnoDB     |
| business     | varchardemo1                                                     | InnoDB     |
| business     | varchardemo2                                                     | InnoDB     |
| business     | presenthistory                                                   | InnoDB     |
| business     | pasthistory                                                      | InnoDB     |
| business     | columnvaluenulldemo                                              | InnoDB     |
| business     | demoemptyandnull                                                 | InnoDB     |
| business     | nonasciidemo                                                     | InnoDB     |
| business     | nullwithselect                                                   | InnoDB     |
| business     | autoincrement                                                    | InnoDB     |
| business     | unsigneddemo                                                     | InnoDB     |
| education    | university                                                       | InnoDB     |
| education    | student                                                          | InnoDB     |
| business     | dateadddemo                                                      | InnoDB     |
| business     | distinctdemo                                                     | InnoDB     |
| business     | groupdemo1                                                       | InnoDB     |
| business     | incasesensdemo                                                   | InnoDB     |
| business     | demo1                                                            | InnoDB     |
| business     | spacecolumn                                                      | InnoDB     |
| business     | milliseconddemo                                                  | InnoDB     |
| business     | primarytabledemo                                                 | InnoDB     |
| business     | foreigntabledemo                                                 | InnoDB     |
| business     | trailingandleadingdemo                                           | InnoDB     |
| business     | duplicatefound                                                   | InnoDB     |
| business     | sequencedemo                                                     | InnoDB     |
| business     | findandreplacedemo                                               | InnoDB     |
| business     | limitoffsetdemo                                                  | InnoDB     |
| business     | deletedemo                                                       | InnoDB     |
| business     | deleterecord                                                     | InnoDB     |
| business     | clonestudent                                                     | InnoDB     |
| business     | firsttable                                                       | InnoDB     |
| business     | secondtable                                                      | InnoDB     |
| business     | rowstranspose                                                    | InnoDB     |
| business     | rowstransposedemo                                                | InnoDB     |
| business     | rownumberdemo                                                    | InnoDB     |
| business     | tblfirst                                                         | InnoDB     |
| business     | updtable                                                         | InnoDB     |
| business     | transcationdemo                                                  | InnoDB     |
| business     | mytable1                                                         | InnoDB     |
| business     | columnexistdemo                                                  | InnoDB     |
| business     | numbercolumndemo                                                 | InnoDB     |
| business     | timestampdemo                                                    | InnoDB     |
| business     | escapedeom                                                       | InnoDB     |
| business     | rowcountdemo                                                     | InnoDB     |
| business     | addconstraintdemo                                                | InnoDB     |
| business     | addcheckconstraintdemo                                           | InnoDB     |
| business     | checkdemo                                                        | InnoDB     |
| business     | alphademo                                                        | InnoDB     |
| business     | imagedemo                                                        | InnoDB     |
| business     | tblifdemo                                                        | InnoDB     |
| business     | tblupdatelimit                                                   | InnoDB     |
| business     | sortingvarchardemo                                               | InnoDB     |
| business     | backticksymbol                                                   | InnoDB     |
| business     | qutesdemo                                                        | InnoDB     |
| business     | ondemo                                                           | InnoDB     |
| business     | xmldemo                                                          | InnoDB     |
| business     | ifelsedemo                                                       | InnoDB     |
| business     | demowhere                                                        | InnoDB     |
| business     | indexingdemo                                                     | InnoDB     |
| business     | functiontriggersdemo                                             | InnoDB     |
| business     | tblfunctiontrigger                                               | InnoDB     |
| business     | triggedemo                                                       | InnoDB     |
| business     | usernameandpassworddemo                                          | InnoDB     |
| business     | tbldemotrail                                                     | InnoDB     |
| business     | tblp                                                             | InnoDB     |
| business     | tblf                                                             | InnoDB     |
| business     | tblselectdemo                                                    | InnoDB     |
| business     | commaseperatedemo                                                | InnoDB     |
| business     | nextpreviousdemo                                                 | InnoDB     |
| business     | multiplerecordwithvalues                                         | InnoDB     |
| business     | multipleindexdemo                                                | InnoDB     |
| business     | trigger1                                                         | InnoDB     |
| business     | trigger2demo                                                     | InnoDB     |
| business     | distinctdemo1                                                    | InnoDB     |
| business     | currentdatetime                                                  | InnoDB     |
| business     | addnotnulldemo                                                   | InnoDB     |
| business     | uniquedemo                                                       | InnoDB     |
| business     | uniqueconstdemo                                                  | InnoDB     |
| business     | distcountdemo                                                    | InnoDB     |
| business     | trimdemo                                                         | InnoDB     |
| business     | trimdemo2                                                        | InnoDB     |
| business     | newstudent                                                       | InnoDB     |
| business     | keydemo                                                          | InnoDB     |
| business     | singlequotesdemo                                                 | InnoDB     |
| business     | functionindexdemo                                                | InnoDB     |
| business     | saveoutputintext                                                 | InnoDB     |
| business     | groupt_concatdemo                                                | InnoDB     |
| business     | groupconcatenatedemo                                             | InnoDB     |
| business     | employeerecords                                                  | InnoDB     |
| business     | myisamtoinnodbdemo                                               | InnoDB     |
| test         | myisamtoinnodbdemo                                               | InnoDB     |
| business     | table1                                                           | InnoDB     |
| business     | table2                                                           | InnoDB     |
| business     | sourcetable                                                      | InnoDB     |
| test         | destination                                                      | InnoDB     |
| business     | originaltable                                                    | InnoDB     |
| test         | originaltable                                                    | InnoDB     |
| business     | saveintotextfile                                                 | InnoDB     |
| business     | tabledemo                                                        | InnoDB     |
| business     | studenttable                                                     | InnoDB     |
| business     | truefalsetable                                                   | InnoDB     |
| business     | uniondemo1                                                       | InnoDB     |
| business     | uniondemo2                                                       | InnoDB     |
| business     | commentdemo                                                      | InnoDB     |
| business     | commentdemo2                                                     | InnoDB     |
| business     | commentdemo3                                                     | InnoDB     |
| business     | tblnull                                                          | InnoDB     |
| business     | proctabledemo                                                    | InnoDB     |
| business     | enumdemo                                                         | InnoDB     |
| business     | enumvalues                                                       | InnoDB     |
| business     | nextiddemo                                                       | InnoDB     |
| business     | firsttabledemo                                                   | InnoDB     |
| business     | secondtabledemo                                                  | InnoDB     |
| business     | duplicatedeletedemo                                              | InnoDB     |
| business     | variablenametable                                                | InnoDB     |
| business     | variableastablename                                              | InnoDB     |
| business     | tableforeign                                                     | InnoDB     |
| business     | tablepri                                                         | InnoDB     |
| business     | tabledemo2                                                       | InnoDB     |
| business     | tabledemo3                                                       | InnoDB     |
| business     | numberofcolumns                                                  | InnoDB     |
| business     | texttabledemo                                                    | InnoDB     |
| business     | blobtabledemo                                                    | InnoDB     |
| business     | tttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttt | InnoDB     |
| business     | college                                                          | InnoDB     |
| business     | studentenrollment                                                | InnoDB     |
| business     | randoptimizedemo                                                 | InnoDB     |
| business     | randomoptimizationdemo                                           | InnoDB     |
| business     | timestamptodatedemo                                              | InnoDB     |
| business     | tablename1tablename1tablename1tablename1tablename1tablename1demo | InnoDB     |
| business     | last10recordsdemo                                                | InnoDB     |
| business     | bitdemo                                                          | InnoDB     |
| business     | tinyintdemo                                                      | InnoDB     |
| business     | booleandemo                                                      | InnoDB     |
| business     | tinyint1demo                                                     | InnoDB     |
| business     | uniquedemo1                                                      | InnoDB     |
| business     | lowcardinality                                                   | InnoDB     |
| business     | ipv4addressdemo                                                  | InnoDB     |
| business     | stringtodatedemo                                                 | InnoDB     |
| business     | wordcountdemo                                                    | InnoDB     |
| business     | columnnameasnumberdemo                                           | InnoDB     |
| business     | columnslist                                                      | InnoDB     |
| business     | jasonasmysqldemo                                                 | InnoDB     |
| business     | insubquerydemo                                                   | InnoDB     |
| business     | orderdemo                                                        | InnoDB     |
| business     | rowintocolumn                                                    | InnoDB     |
| business     | colortable                                                       | InnoDB     |
| business     | finddemo                                                         | InnoDB     |
| business     | uniqueautoid                                                     | InnoDB     |
| business     | countrycitydemo                                                  | InnoDB     |
| business     | schemadatabasemethoddemo                                         | InnoDB     |
| business     | employeeinformation                                              | InnoDB     |
| business     | addingunique                                                     | InnoDB     |
| business     | ipv6demo                                                         | InnoDB     |
| business     | add1daydemo                                                      | InnoDB     |
+--------------+------------------------------------------------------------------+------------+
194 rows in set (0.01 sec)
raja
Published on 23-Nov-2018 12:21:10
Advertisements