Solved

How to get list of Innodb tables in a MySQL database

Posted on 2009-05-05
4
855 Views
Last Modified: 2012-08-13
Anyone know a quick way to generate a list of Innodb tables in a database?
I hosed my ibdata files and need to figure out which tables are Innodb so I can restore them from a backup file.

-G
0
Comment
Question by:gmanpert
  • 2
  • 2
4 Comments
 
LVL 14

Expert Comment

by:racek
ID: 24307202
SELECT * FROM information_schema.`TABLES` where enging = 'InnoDB'
0
 
LVL 14

Accepted Solution

by:
racek earned 500 total points
ID: 24307210
:-)
SELECT * FROM information_schema.`TABLES` where ENGINE = 'InnoDB'
0
 
LVL 18

Expert Comment

by:Hube02
ID: 24307218
I would use something like the attached code. Assumes you are already connected to the DB
<?php  

  

  $query = 'SHOW TABLES';

  if ($results = mysql_query($query)) {

    $success = true;

    $tables = array();

    if (mysql_num_rows($results) > 0) {

      while ($row = mysql_fetch_row($results)) {

        $tables[] = $row[0];

      }

    }

  }

  

  // the array $tables now holds a list of tables in the database

    

  

?>

Open in new window

0
 
LVL 18

Expert Comment

by:Hube02
ID: 24307232
Sorry, missed the InnoDB protion of the question. Better to go with racek.
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

A lot of articles have been written on splitting mysqldump and grabbing the required tables. A long while back, when Shlomi (http://code.openark.org/blog/mysql/on-restoring-a-single-table-from-mysqldump) had suggested a “sed” way, I actually shell …
Introduction In this installment of my SQL tidbits, I will be looking at parsing Extensible Markup Language (XML) directly passed as string parameters to MySQL 5.1.5 or higher. These would be instances where LOAD_FILE (http://dev.mysql.com/doc/refm…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now