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.

Fig. 1 DBMS_METADATA.GET_DDL in Oracle
Fig. 2 sp_helptext in MS SQL Server

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.  

Leave A Comment

Your email address will not be published. Required fields are marked *