Describe each table's columns

Question

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?

Answer 1

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)