MySQL DDL Commands

Posted by Adrian Wed, 20 May 2009 20:43:00 GMT

Here at Component Workshop, we like to automate the management of our database systems as much as possible. We especially like to write tools that can compare and upgrade schemas. To do this, we need to access special data-definition information that was used to create the objects in the first place. This article briefly lists the commands you can use to do this in MySQL

With Oracle there is a dedicated DDL package for this very job. Microsoft's SQL Server used have the SYS tables, and now has the much improved 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 CREATE TABLE sql statement used to create the table

SHOW COLUMNS FROM table_name; and
DESCRIBE table_name;

The two commands are equivalent. They list the columns for a given table in a results set, with type information and other meta data. You do not put quotes around the table name.

Finally, there is a simple way to rename a table:

RENAME TABLE current_name TO new_name;

About

We are a small British company that produces business-oriented software and solutions. These articles are a product of our daily work - information that we think might be useful to share. We hope you find them useful.

Our Software

These are some of our products. Several are open source, some are web-based and others are proprietary:

Syndicate

Articles

Component Workshop Ltd