I'm doing table analysis to understand each table column in a database. Currently, I know how to list all the tables details.

select
    TABLE_SCHEMA ,
    TABLE_NAME ,
    TABLE_TYPE ,
    TABLE_ROWS
from
    information_schema.TABLES t
where
    TABLE_NAME like '%site%'
    and TABLE_TYPE = 'BASE TABLE'

This will result in defining table schema, table name and table rows.

How can I do the same but using DESCRIBE to list all table columns?

CodePudding user response：

You could use information_schema.COLUMNS to get all the fields returned by describe command.

Try:

select TABLE_SCHEMA as 'database',
       TABLE_NAME as 'table',
       COLUMN_NAME as 'Field',
       DATA_TYPE as 'Type',
       IS_NULLABLE as 'Null',
       COLUMN_KEY as 'Key',
       COLUMN_DEFAULT as 'Default',
       EXTRA as 'Extra' 
from information_schema.COLUMNS;

Note. Add specific condition if you want to limit the search for specific schema or table like:

WHERE TABLE_SCHEMA='your_schema' AND TABLE_NAME='your_table_name'

EDIT Tested on my server

mysql> select TABLE_SCHEMA as 'database',
       TABLE_NAME as 'table',
           COLUMN_NAME as 'Field',
           DATA_TYPE as 'Type',
           IS_NULLABLE as 'Null',
           COLUMN_KEY as 'Key',
           COLUMN_DEFAULT as 'Default',
           EXTRA as 'Extra'
from information_schema.COLUMNS
WHERE TABLE_SCHEMA='gesti' AND TABLE_NAME='test_table';
 ---------- ------------ ------- ---------- ------ ----- --------- ---------------- 
| database | table      | Field | Type     | Null | Key | Default | Extra          |
 ---------- ------------ ------- ---------- ------ ----- --------- ---------------- 
| gesti    | test_table | id    | int      | NO   | PRI | NULL    | auto_increment |
| gesti    | test_table | title | longtext | NO   | MUL | NULL    |                |
 ---------- ------------ ------- ---------- ------ ----- --------- ---------------- 
2 rows in set (0.01 sec)

mysql> desc test_table;
 ------- ---------- ------ ----- --------- ---------------- 
| Field | Type     | Null | Key | Default | Extra          |
 ------- ---------- ------ ----- --------- ---------------- 
| id    | int      | NO   | PRI | NULL    | auto_increment |
| title | longtext | NO   | MUL | NULL    |                |
 ------- ---------- ------ ----- --------- ---------------- 
2 rows in set (0.00 sec)

CodePudding user response：

Do not use DESCRIBE or INFORMATION_SCHEMA.COLUMNS. These statements produces partial information, and sometimes it looks like incorrect one even.

Example:

CREATE TABLE test (f1 INT, f2 INT, f3 INT, f4 INT DEFAULT NULL,
                   PRIMARY KEY (f1, f2),
                   UNIQUE (f3, f1),
                   INDEX (f1, f4))

DESC test

Field | Type    | Null | Key | Default | Extra
:---- | :------ | :--- | :-- | :------ | :----
f1    | int(11) | NO   | PRI | null    |      
f2    | int(11) | NO   | PRI | null    |      
f3    | int(11) | YES  | MUL | null    |      
f4    | int(11) | YES  |     | null    |      

select TABLE_SCHEMA as 'database',
       TABLE_NAME as 'table',
       COLUMN_NAME as 'Field',
       DATA_TYPE as 'Type',
       IS_NULLABLE as 'Null',
       COLUMN_KEY as 'Key',
       COLUMN_DEFAULT as 'Default',
       EXTRA as 'Extra' 
from information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()

database      | table | Field | Type | Null | Key | Default | Extra
:------------ | :---- | :---- | :--- | :--- | :-- | :------ | :----
db_1611616104 | test  | f1    | int  | NO   | PRI | null    |      
db_1611616104 | test  | f2    | int  | NO   | PRI | null    |      
db_1611616104 | test  | f3    | int  | YES  | MUL | null    |      
db_1611616104 | test  | f4    | int  | YES  |     | null    |      

SHOW CREATE TABLE test

CREATE TABLE `test` (
 `f1` int(11) NOT NULL,
 `f2` int(11) NOT NULL,
 `f3` int(11) DEFAULT NULL,
 `f4` int(11) DEFAULT NULL,
 PRIMARY KEY (`f1`,`f2`),
 UNIQUE KEY `f3` (`f3`,`f1`),
 KEY `f1` (`f1`,`f4`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

db<>fiddle here

1. The information about the indices provided by both DESCRIBE and INFORMATION_SCHEMA.COLUMNS is partial and does not allow to understand what indices exists in the table really.
2. The output of both DESCRIBE and INFORMATION_SCHEMA.COLUMNS does not allow to understand does the default value is NULL or is not set. Moreover, both DESCRIBE and INFORMATION_SCHEMA.COLUMNS shows that the default values for the columns involved into the primary key are NULL.

So only SHOW CREATE TABLE gives you the complete information about the table structure.