≡ Menu

Overview of MySQL information_schema Database With Practical Examples

[MySQL Information Schema]Starting from MySQL 5, when you execute show databases command, information_schema will be listed in the database list. information_schema is a virtual MySQL database that stores metadata information of all the MySQL databases.

Typically you will execute either DESCRIBE or SHOW commands to get the metadata information. However using the information_schema views, you can execute the standard select SQL command to get the metadata information. In this article, let us review few practical examples on how to use the information_schema database.

1. Get basic information about information_schema

show databases command will display the information_schema in the database list. information_schema is not a physical database.  When you perform select on the information_schema tables, it pulls the data from the real database and other system variables.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| bugs               |
| mysql              |
| sugarcrm           |
+--------------------+
4 rows in set (0.00 sec)

Following are the tables (views) available in the information_schema database.

mysql> use information_schema;
mysql> show tables;
+---------------------------------------+
| Tables_in_information_schema          |
+---------------------------------------+
| CHARACTER_SETS                        |
| COLLATIONS                            |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS                               |
| COLUMN_PRIVILEGES                     |
| KEY_COLUMN_USAGE                      |
| PROFILING                             |
| ROUTINES                              |
| SCHEMATA                              |
| SCHEMA_PRIVILEGES                     |
| STATISTICS                            |
| TABLES                                |
| TABLE_CONSTRAINTS                     |
| TABLE_PRIVILEGES                      |
| TRIGGERS                              |
| USER_PRIVILEGES                       |
| VIEWS                                 |
+---------------------------------------+
17 rows in set (0.00 sec)

2. Query to display tables with more than 1000 rows

Using the above mentioned information_schema tables you can build your own query to get metadata information. This example displays the MySQL tables that have more than 1000 rows.

mysql> select concat(table_schema,'.',table_name) as table_name,table_rows
    -> from information_schema.tables where table_rows > 1000
    -> order by table_rows desc;

+----------------------------------+------------+
| table_name                       | table_rows |
+----------------------------------+------------+
| bugs.series_data                 |      52778 |
| bugs.bugs_activity               |      26436 |
| bugs.longdescs                   |      21473 |
| bugs.email_setting               |       5370 |
| bugs.attachments                 |       4714 |
| bugs.attach_data                 |       4651 |
| bugs.cc                          |       4031 |
| bugs.bugs                        |       2190 |
| bugs.namedqueries_link_in_footer |       1228 |
+----------------------------------+------------+
9 rows in set (0.04 sec)

3. Query to list all tables without primary key

This example gives a list of all the tables without primary key.

SELECT CONCAT(t.table_name,".",t.table_schema) as table_name
FROM information_schema.TABLES t
LEFT JOIN information_schema.TABLE_CONSTRAINTS tc
ON t.table_schema = tc.table_schema
AND t.table_name = tc.table_name
AND tc.constraint_type = 'PRIMARY KEY'
WHERE tc.constraint_name IS NULL
AND t.table_type = 'BASE TABLE';

4. Implement history data for tables using information_schema

Putting the MySQL information_schema to Use article implements a history database using the information schema. The first half of this article describes the requirements for the history database, and a generic design to implement it. The second half describes the stepwise construction of code-generator that creates the SQL to construct and load the history database. The code-generator is driven by the information schema and some features of the information schema are discussed in detail.

5. Query to list top 5 largest tables in the database

This examples gives the top 5 largest space occupying tables in the database along with it’s size in MB.

mysql> SELECT concat(table_schema,'.',table_name) table_name,
    -> concat(round(data_length/(1024*1024),2),'M') data_length
    -> FROM information_schema.TABLES
    -> ORDER BY data_length DESC LIMIT 5;

+--------------------+-------------+
| table_name         | data_length |
+--------------------+-------------+
| bugs.attach_data   | 706.89M     |
| bugs.longdescs     | 3.45M       |
| bugs.bugs_activity | 1.45M       |
| bugs.series_data   | 0.75M       |
| bugs.attachments   | 0.51M       |
+--------------------+-------------+
5 rows in set (0.05 sec)

Additional References:

Add your comment

If you enjoyed this article, you might also like..

  1. 50 Linux Sysadmin Tutorials
  2. 50 Most Frequently Used Linux Commands (With Examples)
  3. Top 25 Best Linux Performance Monitoring and Debugging Tools
  4. Mommy, I found it! – 15 Practical Linux Find Command Examples
  5. Linux 101 Hacks 2nd Edition eBook Linux 101 Hacks Book

Bash 101 Hacks Book Sed and Awk 101 Hacks Book Nagios Core 3 Book Vim 101 Hacks Book

Comments on this entry are closed.

  • Leslie Satenstein Montreal Canada April 18, 2009, 12:49 pm

    Hi Ramesh.

    In working with some sophisticated ERPs, (Baan), their sql for retrievals appears like this

    select xxxxxxx
    from xxxxxxxx
    where
    xxxxxxx
    selectdo
    xxxxxxx
    selectempty
    xxxxxxxx
    selecteos
    xxxxxxxxx
    endselect

    The where clause can have other selects (recursive). such as from where exists (select … )
    The where clause always did a left join, so the programmer decided if he wanted
    where a = b,
    or wanted
    where b=a

    selectdo
    loop here until empty
    selecteos
    after the last match
    selectempty
    if there is no match
    endselect

    The from clause indicated if it was for update, etc. A break command and continue are supported in the seledtdo as well as the selecteos.

    How difficult is it to develop this set of statements as a pre-parser expression for mysql, or oracle, or postgressql ?

  • JuanPablo December 10, 2009, 7:16 pm

    hi,

    how I can use select table_name from information_schema.tables where table_schema=”miDB” in a query ?

    and make same query over every table of mi db?

    thanks.

  • Anshul Agarwal March 31, 2010, 12:48 pm

    Hi!
    Plz tell me how can i retrieve the column names of a table from the information schema.

  • Pushpesh Singh April 29, 2010, 1:47 am

    Hi Anshul,

    You can use the following query to retrieve all the column names for a given database :

    SELECT DISTINCT `COLUMN_NAME`
    FROM `COLUMNS`
    WHERE `TABLE_SCHEMA` = ‘YOUR_DATABASE_NAME_HERE’
    AND `TABLE_NAME` = ‘YOUR_TABLE_NAME_HERE’;

    hope it helps! 🙂

    Regards,

    Pushpesh

  • Pushpesh Singh April 29, 2010, 1:57 am

    Hi JuanPablo,

    You can go like this :

    $link = mysql_connect(‘host’, ‘user’, ‘password’);
    mysql_select_db(‘database’, $link);

    $sql = ‘select table_name from information_schema.tables where table_schema=”miDB”‘;
    $run = mysql_query($sql, $link) or die(mysql_error());

    if($run && mysql_num_rows($run) > 0) {
    $query = ‘YOUR_QUERY_HERE’;

    mysql_select_db(“miDB”, $link);

    while(($fetch = mysql_fetch_assoc($run)) !== false) {
    $exec = mysql_query($query, $link);

    // process your o/p here….
    }
    }

    hope it helps ! 🙂

    Regards,

    Pushpesh

  • Malaney July 31, 2010, 9:44 pm

    Great article! Just a small correction to your query to retrieve 5 largest tables. As it is written above, the query will order alphabetically, so that 9M comes before 122M. Use the sql “abs” function to order data_length numerically. Your query above becomes:

    mysql> SELECT concat(table_schema,’.’,table_name) table_name,
    -> concat(round(data_length/(1024*1024),2),’M’) data_length
    -> FROM information_schema.TABLES
    -> ORDER BY abs(data_length) DESC LIMIT 5;

  • Saravanan May 31, 2013, 5:08 am

    hi,

    New to Mysql

    is there any way to find in mysql , the stored procedures which uses any given table ?

    In oracle we can do that by querying user_objects/user_triggers/user_procedures to find out which procedure or function .or package is using a table

    Is this possible in MYSQL ?

  • Jayadev Swain October 24, 2013, 5:27 am

    Ramesh,

    Finally after much searching online I found a decently explained article on how to access the details in the Information_schema.

    Thanks!

  • Megan Squire April 29, 2015, 5:48 pm

    (1) The query to show tables without a Primary Key should use an OUTER JOIN not an INNER JOIN.
    The query should be:
    SELECT CONCAT( t.table_name, “.”, t.table_schema ) AS table_name
    FROM information_schema.TABLES t
    LEFT OUTER JOIN information_schema.TABLE_CONSTRAINTS tc ON t.table_schema = tc.table_schema
    AND t.table_name = tc.table_name
    AND tc.constraint_type = ‘PRIMARY KEY’
    WHERE tc.constraint_name IS NULL
    AND t.table_type = ‘BASE TABLE’

    (2) Malaney (commenter) above is correct that the last query does not work as written. His solution is fine, or another correct solution is just to rename the column alias to something besides “data_length” as that is ambiguous. This works:

    SELECT CONCAT( table_schema, ‘.’, table_name ) AS table_name, CONCAT( ROUND( data_length / ( 1024 *1024 ) , 2 ) , ‘M’ ) AS len_rows
    FROM information_schema.TABLES
    ORDER BY data_length DESC
    LIMIT 5;