MySQL Data Catalog
The following is a nice diagram of the MySQL Database Catalog. The database catalog is located in the information_schema database. If you click on the image file, it’ll take you to the site where the original Visio File is found. The drawing uses the Information Engineering notation. You can also download it from there.
What may appear as a downside of the information_schema is that you can’t grant even select privileges to external users. You’d get the following if you tried to do so as the root user.
mysql> GRANT SELECT ON information_schema.* TO student; ERROR 1044 (42000): Access denied FOR USER 'root'@'localhost' TO DATABASE 'information_schema' |
There’s a reason for this behavior. You already have select privileges by default because the information_schema is a query only repository. Therefore, it is open to all users.
Here’s a quick example of accessing them from inside another database, which requires that you reference the tables with the owning database name.
SELECT t.table_name , c.column_name FROM information_schema.tables t JOIN information_schema.columns c ON t.table_name = c.table_name WHERE t.table_schema = 'SAMPLEDB'; |
You also have the ability to list tables in any MySQL database by typing the show tables command:
SHOW TABLES; |
You also have the ability to list tables you’ve created by typing show create table table_name command:
SHOW CREATE TABLE TABLE_NAME |
