The ANSI standard makes it fairly straight forward to retrieve metadata from a database server through the use of the INFORMATION_SCHEMA. For further convienience MySQL uses the SHOW syntax. SHOW TABLES, SHOW DATABASES, etc.

SHOW DATABASES returns a list of the databases withing the current server instance.

 mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | kennyl | | mysql | | performance_schema | | tomcatusers | | wordpress | +--------------------+ 6 rows in set (0.00 sec)

SHOW TABLES; shows all the tables in the selected database.

 mysql> SHOW TABLES; +--------------------+ | Tables_in_kennyl | +--------------------+ | account | | activity | | address | | comments | | contact | | phone | +--------------------+ 39 rows in set (0.92 sec) 

SHOW COLUMNS FROM [tablename]; displays all the columns for for tablename

 mysql> SHOW COLUMNS FROM Phone; +-------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------+------+-----+---------+-------+ | RowId | varchar(15) | NO | PRI | NULL | | | Type | varchar(15) | YES | | NULL | | | CountryCode | char(3) | YES | | NULL | | | AreaCode | char(3) | NO | | NULL | | | Prefix | char(3) | NO | | NULL | | | Number | char(4) | NO | | NULL | | | CommentId | varchar(15) | YES | | NULL | | +-------------+-------------+------+-----+---------+-------+ 13 rows in set (0.09 sec) 																

SHOW PROCEDURE STATUS displays a list of store procedures that have been stored on the selected database.

 mysql> SHOW PROCEDURE STATUS; +--------+--------------+-----------+----------------+---------------------+---------------------+---- | Db | Name | Type | Definer | Modified | Created | Sec +--------+--------------+-----------+----------------+---------------------+---------------------+---- | kennyl | CreateApp | PROCEDURE | root@localhost | 2012-03-15 17:01:40 | 2012-03-15 17:01:40 | DEF... | kennyl | CreatePage | PROCEDURE | root@localhost | 2012-03-15 16:58:20 | 2012-03-15 16:58:20 | DEF... | kennyl | GetAppId | PROCEDURE | root@localhost | 2012-03-16 13:13:17 | 2012-03-16 13:13:17 | DEF... | kennyl | getNextPlace | PROCEDURE | root@localhost | 2012-03-15 14:35:55 | 2012-03-15 14:35:55 | DEF... | kennyl | getNextRowId | PROCEDURE | root@% | 2012-05-29 12:23:35 | 2012-05-29 12:23:35 | DEF... | kennyl | GetPages | PROCEDURE | root@localhost | 2012-03-16 13:01:13 | 2012-03-16 13:01:13 | DEF... | kennyl | GetSubPages | PROCEDURE | root@localhost | 2012-03-16 13:31:49 | 2012-03-16 13:31:49 | DEF... | kennyl | simpleProc | PROCEDURE | root@localhost | 2012-03-15 13:49:45 | 2012-03-15 13:49:45 | DEF... +--------+--------------+-----------+----------------+---------------------+---------------------+---- 8 rows in set (0.00 sec) 																

The result has been truncated, the list also includes Security_type, Comment, character_set_client, collation_connection and Database_collation. I think the main interest is in Db, Name and type.

SHOW FUNCTION STATUS;

SHOW FUNCTION STATUS displays a list of functions that have been stored on the selected database.