![[MySQL Information Schema]](https://static.thegeekstuff.com/wp-content/uploads/2008/11/information-schema.jpg "[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)
My name is Ramesh Natarajan. I will be posting instruction guides, how-to, troubleshooting tips and tricks on Linux, database, hardware, security and web. My focus is to write articles that will either teach you or help you resolve a problem. Read more about
Comments on this entry are closed.
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 ?
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.
Hi!
Plz tell me how can i retrieve the column names of a table from the information schema.
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
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
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;
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 ?
Ramesh,
Finally after much searching online I found a decently explained article on how to access the details in the Information_schema.
Thanks!
(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;