Working with Oracle and SQL Server I have become used to not-so-simple statements or procedures for extracting the structure of database objects. In order to extract the DDL for a database object, one would typically use a third party tool such as SQL Navigator or Toad. An alternative for more advanced DBAs would be to use DBMS_METADATA.GET_DDL. In SQL Server either you script the object from SQL Server Management Studio or you use sp_helptext for procedures for example.
There is actually a very interesting way this is done in MySQL. It is a very simple SHOW command that is quite easy to remember and extend:
SHOW CREATE TABLE <TABLENAME>
I found this particularly interesting because once you know the basic syntax, it become almost intuitive. Other commands exist such as the following to display on indexes on a table :
SHOW INDEX FROM <TABLE_NAME>
I am sure you may already be familiar with SHOW DATABASE, SHOW TABLES, SHOW PROCESSLIST, SHOW PRIVILEGES etc. A full list of MySQL’s SHOW commands can be found here.