MySQL DDL Commands
At Arctus we like to automate the management of our database systems. We especially like to write tools that can compare and upgrade schemas. To do this, we need to access the Data Definition Language (DDL) that created the objects in the first place. In Oracle there is a dedicated DDL package for this very job and in SQL Server we have the SYS tables (and the new INFORMATION_SCHEMA views).
MySQL does it a slightly different way, and the following commands are extremely useful:
| SHOW TABLES; | Lists all tables by name |
| SHOW TABLE STATUS; | Lists all tables with additional information – date created, number of rows etc. |
| SHOW CREATE TABLE table_name | Shows the DDL used to create the table |
| SHOW COLUMNS FROM table_name | Lists the columns for a given table in a results set, with type information and other metadata |
Finally, like SQL Server, there is a way of renaming a table:
RENAME TABLE current_name TO new_name;
If (like us) you occasionally have to deal with MySQL but it is not your primary database, we hope you find this little list of commands useful.
