Sql query table name and description table field (column) information in MySql database

The following is to introduce how to use the SQL query statement to obtain the table name of the table in the Mysql database, table description, field ID, field name, data type, length, precision, whether it can be null, default value, self-increment, primary key, column description.

Query table information (table name / table description)

SELECT table_name name,TABLE_COMMENT value FROM INFORMATION_SCHEMA.TABLES WHERE table_type='base table' and table_schema = 'database name' order by table_name asc

2. Query field information (field ID/ field name / data type / length / precision / whether it can be null/ default value / self-increment / primary key / column description)

Method 1:

SHOW FULL COLUMNS FROM table name

Method 2:

Select ORDINAL_POSITION as Colorder,Column_Name as ColumnName,data_type as TypeName,COLUMN_COMMENT as DeText, (case when data_type = 'float' or data_type =' double' or data_type = 'decimal' then NUMERIC_PRECISION else CHARACTER_MAXIMUM_LENGTH end) as length,NUMERIC_SCALE as Scale, (case when EXTRA='auto_increment' then 1 else 0 end) as IsIdentity, (case when COLUMN_KEY='PRI' then 1 else 0 end) as IsPK, (case when IS_NULLABLE =' NO' then 0 else 1 end) as CanNull COLUMN_DEFAULT as DefaultValfrom information_schema.columns where table_schema = 'database name' and table_name = 'table name' order by ORDINAL_POSITION asc

These are several ways for Sql to obtain column information such as table names and field names and data types in the MySql database. If it is not what you need, you can also take a look at the related articles below.