information_schema(wich is available as of version 5.0.2) is a very powerful feature that can help you maintain or administrate your databases. You can use it to investigate the existence and status of database objects (such as tables, constraints and indexes) and their components (such as columns).
I like to use the MySQL command line tool for lots of common tasks. From the command line tool, I frequently feel the need to query the information_schema. In most cases, I just need to have a quick overview of a database's assets. In fact, I took up the habit in my job as an Oracle developer. In the Oracle command line tool, SQL*Plus, I'd do something like:
from all_objects o
where owner = 'SCOTT'
order by object_type
and this would give me a list of all the objects accessible to the current user that reside in the schema owned by the user
SCOTT. For each object, the schema in wich the object resides would be listed, along the object's name and the date (actually the datetime, but the formatting leaves out the time component) the object was created:
OBJECT_NAME OBJECT_TYPE CREATED
------------------------------ ------------------ --------
PK_DEPT INDEX 12-05-02
PK_EMP INDEX 12-05-02
PK_NODE INDEX 18-08-05
SYS_C003897 INDEX 09-08-05
UK_NODE1 INDEX 18-08-05
UK_NODE2 INDEX 18-08-05
EMPREPORT PACKAGE 20-03-05
EMPREPORT PACKAGE BODY 20-03-05
BONUS TABLE 12-05-02
DEPT TABLE 12-05-02
EMP TABLE 12-05-02
IMAGES TABLE 20-03-05
NODE TABLE 18-08-05
PK TABLE 09-08-05
PRODUCT TABLE 18-08-05
SALGRADE TABLE 12-05-02
With the mysql information_schema database, we can achieve the same sort of list. However, there's no table there that contains all these different types of objects.
Another thing that can be a bit frustrating is that such a query can be quite lengthy. The identifiers used throughout the
information_schemadatabase are quit long and verbose.
select table_name identifier
where table_schema = 'information_schema'
where table_schema = 'information_schema'
| avg_character_length | min_character_length | max_character_length |
| 13.1279 | 2 | 37 |
On the one hand, these elaborate identifiers make it easy to understand the internal structure of the
information_schemaand the queries that are built upon it, wich is good. On the other hand, it easily becomes a nuisance when you have to type them over and over again.
On top of that, I usually query the
information_schemadatabase in a situation where it is not the current database. Lazy me just wants to perform an ad hoc query to get some information on the current database, and not switch databases first with a
All in all, in it's raw form, the
information_schemais not that suitable for quickly inspecting a database. I decided to make life easier for myself, and some time ago, I created a database to store all kinds of general purpose utilities.
create database utils
Among my utilities are a couple of views built on the
information_schema. In these views, I tried to find a balance between ease of use and comprehensibility. I'm printing them all here, hoping someone else will benefit.
First of all, there's the
create or replace
, _utf8' '
, _utf8' ON '
, _utf8' FOR EACH '
) as extra
, _utf8' DETERMINISTIC '
, ' SQL SECURITY '
) as extra
, ' ROW_FORMAT='
) as extra
objsview yields one row for each database object. I decided to include the following objects: base tables, (system) views, procedures, functions and triggers. I decided to leave out indexes, because you can't identify an index by name in a given database: you'll also need the table name to be absolutely sure.
The columns are:
- The database (schema) in wich the object resides.
- The namespace. Currently, there are three namespaces: 'TABLES' (base tables, views, system views); 'ROUTINES' (functions, stored procedures) and 'TRIGGERS'
- The name of the object, wich is unique within the namespace given a particular database
- The actual object type. Values include: 'BASE TABLE', 'VIEW' and 'SYSTEM VIEW' for objects in the 'TABLES' namespace; 'FUNCTION' or 'BASE TABLE', 'VIEW' and 'SYSTEM VIEW'for the 'ROUTINES'namespace; and 'TRIGGER' for the 'TRIGGERS'namespace
- This column shows some extra information, dependant upon the object type.
- The datetime corresponding to the that the object was created
- The comments describing the object
Now, to display a list of all the objects in the current database, just do:
where db = schema()
refsview lists information about foreign keys. Here's the code:
, case count(cidcon.constraint_name)
when 0 then 'NO'
from information_schema.table_constraints c
inner join information_schema.key_column_usage kc
on c.table_schema = kc.table_schema
and c.table_name = kc.table_name
and c.constraint_name = kc.constraint_name
inner join information_schema.columns ctcol
on kc.table_schema = ctcol.table_schema
and kc.table_name = ctcol.table_name
and kc.column_name = ctcol.column_name
left join information_schema.key_column_usage cidcol
on kc.table_schema = cidcol.table_schema
and kc.table_name = cidcol.table_name
and kc.column_name = cidcol.column_name
left join information_schema.table_constraints cidcon
on cidcol.constraint_schema = cidcon.constraint_schema
and cidcol.constraint_name = cidcon.constraint_name
and cidcol.table_name = cidcon.table_name
and cidcon.constraint_type != 'FOREIGN KEY'
inner join information_schema.key_column_usage kp
on kc.referenced_table_schema = kp.table_schema
and kc.referenced_table_name = kp.table_name
and kc.referenced_column_name = kp.column_name
and kc.position_in_unique_constraint = kp.ordinal_position
inner join information_schema.table_constraints p
on kp.table_schema = p.table_schema
and kp.table_name = p.table_name
and kp.constraint_name = p.constraint_name
where p.constraint_type != 'FOREIGN KEY'
and c.constraint_type = 'FOREIGN KEY'
group by c.constraint_schema
Apart from the foreign key name and table, the table that is referred to is listed too, along with the primary key or unique constraint of wich the columns are referenced by the foreign key columns. As a bonus, some extra information is shown about the type of relationship implemented by the foreign key.
- The database in wich the foreign key table (and thus, the foreign key constraint) resides.
- The name of the foreign key constraint
- The name of the table with the foreign key constraint a.k.a. the child or detail table
- The database that contains the referenced table
- The unique or primary key constraint of the referenced table that contains the columns that are referred by the foreign key columns
- The type of the referenced constraint: either 'PRIMARY KEY' or 'UNIQUE'
- The referenced (a.k.a master or lookup) table
- Whether the foreign key columns accept
NULLvalues: 'YES' (foreign key columns accept nulls, the relationship is not mandatory) or 'NO' (foreign key columns cannot have nulls, thus the relationship is mandatory)
- Wheter the foreign key columns are also part of a 'PRIMARY KEY' or 'UNIQUE' constraint in the referencing table: 'YES' (foreign key columns are partially identifying) or 'NO' (foreign key columns are not part of an identifier)
refsview does not take into account that MySQL/innodb supports foreign keys that do not refer to either a 'PRIMARY KEY' or a 'UNIQUE' constraint.
(Q: Wow, can MySQL do that? A: Yes, it can! Q: Why would you want to do that anyway? A: uhmm, don't really know, but you can post on this subject if you have a suggestion).
idxsview lists information about indexes. Here's the code:
from information_schema.tables t
inner join information_schema.statistics s
on t.table_schema = s.table_schema
and t.table_name = s.table_name
group by t.table_schema
This view yields one row for each index.
- The database in wich the index table (and thus, the index) resides.
- The name of the index
- The name of the table that defines the index
- Whether the index accepts duplicate values or value combinations: 'YES' when this is an unique index, else 'NO' if the index accepts duplicates
- Whether the index columns accept
NULLvalues: 'YES' (index columns accept nulls) or 'NO' (index columns are mandatory)
- The number of columns in de index
- The maximum selectivity for all columns in the index
- The minimum selectivity for all columns in the index
So, good luck with this everyone. I hope this will be as useful to you as it was to me.