MySQL 5.7 show table columns 3 ways

1 Show MySQL table columns by describe

The simplest and the easiest way to remember is:

describe tablename
Which will give us basic but enough information for most cases:

  • Field
  • Type
  • Null
  • Key
  • Default
  • Extra
describe fiscal

The result of this query is:

Field Type Null Key Default Extra
flag1 varchar(5) YES

2 Show MySQL table columns by quering meta data

We can do a query against MySQL meta data in order to get more information. This will give us additional information as schema, position, privileges and more.

select * from information_schema.columns
where table_schema = 'test'
and  table_name = 'fiscal'
order by table_name,ordinal_position

The fields of this query are:

  • TABLE_CATALOG
  • TABLE_SCHEMA
  • TABLE_NAME
  • COLUMN_NAME
  • ORDINAL_POSITION
  • COLUMN_DEFAULT
  • IS_NULLABLE
  • DATA_TYPE
  • CHARACTER_MAXIMUM_LENGTH
  • CHARACTER_OCTET_LENGTH
  • NUMERIC_PRECISION
  • NUMERIC_SCALE
  • CHARACTER_SET_NAME
  • COLLATION_NAME
  • COLUMN_TYPE
  • COLUMN_KEY
  • EXTRA
  • PRIVILEGES
  • COLUMN_COMMENT

3 Show MySQL table columns as DDL (create code)

The third option is use DDL information of the table. This SQL create table have information for the tables. This will give us basic information about the columns:

  • Field
  • Type
  • Null
  • Key
  • Default
  • Extra
SHOW CREATE TABLE `test`.`fiscal`;

result of this query is:

CREATE TABLE `fiscal` (
  `id` int(11) DEFAULT NULL,
  `year` int(4) DEFAULT NULL,
  `fiscalEnd` varchar(10) COLLATE utf8_bin DEFAULT NULL,
  `flag1` varchar(5) COLLATE utf8_bin DEFAULT NULL,
  `flag2` varchar(3) COLLATE utf8_bin DEFAULT NULL,
  `flag3` varchar(3) COLLATE utf8_bin DEFAULT NULL,
  `result` int(2) DEFAULT NULL,
  `fisRes` date DEFAULT NULL,
  `startDate` date DEFAULT '2018-02-22',
  `endTime` time DEFAULT '22:00:00'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

Related Article