How to describe all tables in the database through one statement in MySQL?

You can use INFORMATION_SCHEMA.COLUMNS to describe all tables in a database with a single statement. The syntax is as follows.

SELECT *FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=’yourDatabaseName’\G

Here I am using a database example with two tables.

The table names are as follows -

• mytable
• yourtable

Implement the above syntax for your database. The query is as follows -

mysql> select * FROM information_schema.columns WHERE table_schema = 'sample'\G

The following is the output describing the two tables in our database.

*************************** 1. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: sample
TABLE_NAME: mytable
COLUMN_NAME: id
ORDINAL_POSITION: 1
COLUMN_DEFAULT: NULL
IS_NULLABLE: YES
DATA_TYPE: int
CHARACTER_MAXIMUM_LENGTH: NULL
CHARACTER_OCTET_LENGTH: NULL
NUMERIC_PRECISION: 10
NUMERIC_SCALE: 0
DATETIME_PRECISION: NULL
CHARACTER_SET_NAME: NULL
COLLATION_NAME: NULL
COLUMN_TYPE: int(11)
COLUMN_KEY:
EXTRA:
PRIVILEGES: select,insert,update,references
COLUMN_COMMENT:
GENERATION_EXPRESSION:
SRS_ID: NULL
*************************** 2. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: sample
TABLE_NAME: mytable
COLUMN_NAME: Name
ORDINAL_POSITION: 2
COLUMN_DEFAULT: NULL
IS_NULLABLE: YES
DATA_TYPE: varchar
CHARACTER_MAXIMUM_LENGTH: 100
CHARACTER_OCTET_LENGTH: 400
NUMERIC_PRECISION: NULL
NUMERIC_SCALE: NULL
DATETIME_PRECISION: NULL
CHARACTER_SET_NAME: utf8mb4
COLLATION_NAME: utf8mb4_0900_ai_ci
COLUMN_TYPE: varchar(100)
COLUMN_KEY:
EXTRA:
PRIVILEGES: select,insert,update,references
COLUMN_COMMENT:
GENERATION_EXPRESSION:
SRS_ID: NULL
*************************** 3. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: sample
TABLE_NAME: yourtable
COLUMN_NAME: id
ORDINAL_POSITION: 1
COLUMN_DEFAULT: NULL
IS_NULLABLE: YES
DATA_TYPE: int
CHARACTER_MAXIMUM_LENGTH: NULL
CHARACTER_OCTET_LENGTH: NULL
NUMERIC_PRECISION: 10
NUMERIC_SCALE: 0
DATETIME_PRECISION: NULL
CHARACTER_SET_NAME: NULL
COLLATION_NAME: NULL
COLUMN_TYPE: int(11)
COLUMN_KEY:
EXTRA:
PRIVILEGES: select,insert,update,references
COLUMN_COMMENT:
GENERATION_EXPRESSION:
SRS_ID: NULL
*************************** 4. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: sample
TABLE_NAME: yourtable
COLUMN_NAME: YourName
ORDINAL_POSITION: 2
COLUMN_DEFAULT: NULL
IS_NULLABLE: YES
DATA_TYPE: varchar
CHARACTER_MAXIMUM_LENGTH: 100
CHARACTER_OCTET_LENGTH: 400
NUMERIC_PRECISION: NULL
NUMERIC_SCALE: NULL
DATETIME_PRECISION: NULL
CHARACTER_SET_NAME: utf8mb4
COLLATION_NAME: utf8mb4_0900_ai_ci
COLUMN_TYPE: varchar(100)
COLUMN_KEY:
EXTRA:
PRIVILEGES: select,insert,update,references
COLUMN_COMMENT:
GENERATION_EXPRESSION:
SRS_ID: NULL
4 rows in set (0.00 sec)

The above is the detailed content of How to describe all tables in the database through one statement in MySQL?. For more information, please follow other related articles on the PHP Chinese website!