Home > Database > Mysql Tutorial > How to describe all tables in the database through one statement in MySQL?

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

王林
Release: 2023-09-03 09:29:05
forward
881 people have browsed it

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
Copy after login

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
Copy after login

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)
Copy after login

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!

Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template