MySQL: obtener descripción de tabla

By: Javier on 10/08/2011

Desde MySQL existen diversas formas de saber los campos de una tabla y obtener diversa información acerca de como fue creada. A continuación se muestran algunas  de las formas de obtener dichos datos.

mysql> show columns from TABLE_NAME;
mysql> desc TABLE_NAME;
mysql> show create table TABLE_NAME;

Un ejemplo de ejecución tras el salto.

mysql> show columns from watchdog;
+-----------+--------------+------+-----+---------+-------------+
| Field     | Type         | Null | Key | Default | Extra       |
+-----------+--------------+------+-----+---------+-------------+
| wid       | int(11)      | NO   | PRI | NULL    | auto_increm |
| uid       | int(11)      | NO   |     | 0       |             |
| type      | varchar(16)  | NO   | MUL |         |             |
| message   | longtext     | NO   |     | NULL    |             |
| variables | longtext     | NO   |     | NULL    |             |
| severity  | tinyint(3)   | NO   |     | 0       |             |
| link      | varchar(255) | NO   |     |         |             |
| location  | text         | NO   |     | NULL    |             |
| referer   | text         | YES  |     | NULL    |             |
| hostname  | varchar(128) | NO   |     |         |             |
| timestamp | int(11)      | NO   |     | 0       |             |
+-----------+--------------+------+-----+---------+-------------+
11 rows in set (0.00 sec)

mysql> desc watchdog;
+-----------+--------------+------+-----+---------+-------------+
| Field     | Type         | Null | Key | Default | Extra       |
+-----------+--------------+------+-----+---------+-------------+
| wid       | int(11)      | NO   | PRI | NULL    | auto_increm |
| uid       | int(11)      | NO   |     | 0       |             |
| type      | varchar(16)  | NO   | MUL |         |             |
| message   | longtext     | NO   |     | NULL    |             |
| variables | longtext     | NO   |     | NULL    |             |
| severity  | tinyint(3)   | NO   |     | 0       |             |
| link      | varchar(255) | NO   |     |         |             |
| location  | text         | NO   |     | NULL    |             |
| referer   | text         | YES  |     | NULL    |             |
| hostname  | varchar(128) | NO   |     |         |             |
| timestamp | int(11)      | NO   |     | 0       |             |
+-----------+--------------+------+-----+---------+-------------+
11 rows in set (0.00 sec)

mysql> show create table watchdog;
+----------+----------------------------------------------------+
| Table    | Create Table                                       |
+----------+----------------------------------------------------+
| watchdog |CREATE TABLE `watchdog` (                           |
|          | `wid` int(11) NOT NULL AUTO_INCREMENT,             |
|          | `uid` int(11) NOT NULL DEFAULT '0',                |
|          | `type` varchar(16) NOT NULL DEFAULT '',            |
|          | `message` longtext NOT NULL,                       |
|          | `variables` longtext NOT NULL,                     |
|          | `severity` tinyint(3) NOT NULL DEFAULT '0',        |
|          | `link` varchar(255) NOT NULL DEFAULT '',           |
|          | `location` text NOT NULL,                          |
|          | `referer` text,                                    |
|          | `hostname` varchar(128) NOT NULL DEFAULT '',       |
|          | `timestamp` int(11) NOT NULL DEFAULT '0',          |
|          | PRIMARY KEY (`wid`),                               |
|          | KEY `type` (`type`)                                |
|          | ) ENGINE=InnoDB DEFAULT CHARSET=latin1             |
+----------+----------------------------------------------------+
1 row in set (0.00 sec)