SHOW COLUMNS is a versatile command in MySQL used to display detailed information about the columns in a specified table. This command is essential for exploring database schemas, understanding column properties, and diagnosing data issues. It provides a comprehensive view of column-level metadata, making it invaluable for data analysts, database administrators, and developers alike.


SHOW COLUMNS FROM `database`.`table_name` [LIKE `pattern`];

Required Arguments:

  • database: The name of the database containing the target table.
  • table_name: The name of the table whose columns you want to inspect.

Optional Arguments:

  • LIKE pattern: A wildcard expression to filter column names. Use % as a wildcard to match any character sequence.




Simple Usage

List all column details for the “users” table in the “mydb” database:

SHOW COLUMNS FROM `mydb`.`users`;

Filtering Columns

Display only columns with names matching the pattern “name%”:

SHOW COLUMNS FROM `mydb`.`users` LIKE 'name%';

Complex Usage

Combine with the WHERE clause to filter columns based on specific criteria, such as data type:

SHOW COLUMNS FROM `mydb`.`users` WHERE `Type` = 'int';

Common Issues

  • Missing Columns: If a column is not listed in the output, it may be because the table doesn’t exist or the user lacks access to it.
  • Truncated Output: If the output is cut off, adjust the terminal window size or use the \G option to display the results in pages.


SHOW COLUMNS can be combined with other commands to perform advanced tasks:

  • ALTER TABLE: Modify column definitions based on the information retrieved from SHOW COLUMNS.
  • EXPLAIN: Analyze query execution plans to identify performance bottlenecks based on column properties.
  • DESC: Provides a concise summary of column information.
  • INFORMATION_SCHEMA.COLUMNS: A system table that stores detailed column information.
  • CREATE TABLE, ALTER TABLE: Commands used to define and modify table schemas.