Overview of MySQL information_schema Database With Practical Examples

by Ramesh Natarajan on November 13, 2008

[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:


Linux Sysadmin Course Linux provides several powerful administrative tools and utilities which will help you to manage your systems effectively. If you don’t know what these tools are and how to use them, you could be spending lot of time trying to perform even the basic administrative tasks. The focus of this course is to help you understand system administration tools, which will help you to become an effective Linux system administrator.
Get the Linux Sysadmin Course Now!

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

{ 8 comments… read them below or add one }

1 Leslie Satenstein Montreal Canada April 18, 2009 at 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 ?

2 JuanPablo December 10, 2009 at 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.

3 Anshul Agarwal March 31, 2010 at 12:48 pm

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

4 Pushpesh Singh April 29, 2010 at 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

5 Pushpesh Singh April 29, 2010 at 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

6 Malaney July 31, 2010 at 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;

7 Saravanan May 31, 2013 at 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 ?

8 Jayadev Swain October 24, 2013 at 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!

Leave a Comment

Previous post:

Next post: