MySQL Show Columns

Last updated: October 19, 2026Oct 19, 2026 4 min read

Before writing a query or an INSERT, you often need to check a table's columns: their names, types, whether they accept NULL, and their defaults. MySQL offers SHOW COLUMNS, its shortcut DESCRIBE, and the information_schema.columns table. This tutorial explains each, using a patients table from a clinic database.

1. SHOW COLUMNS

SHOW [FULL] {COLUMNS | FIELDS} {FROM | IN} table_name [{FROM | IN} db_name]
  [LIKE 'pattern' | WHERE condition];

SHOW COLUMNS FROM patients;

Result: 9 rows

2. Understanding the Output

3. DESCRIBE and DESC

DESCRIBE (or DESC) is a shortcut for SHOW COLUMNS FROM:

DESC doctors;

Result: 2 rows

You can also describe a single column:

DESCRIBE patients phone;

Result: 1 row

4. SHOW FULL COLUMNS

FULL adds the collation, your privileges on each column and the column comment:

SHOW FULL COLUMNS FROM patients WHERE Comment <> '';

Result: 2 rows

5. Filtering Columns

SHOW COLUMNS FROM patients LIKE '%date%';

Result: 1 row

SHOW COLUMNS FROM patients WHERE `Null` = 'NO';

Result: 4 rows

SHOW COLUMNS FROM patients FROM clinic WHERE `Type` LIKE 'varchar%';

Result: 2 rows

6. Using information_schema.columns

For full control (sorting, joining, searching across tables), query the metadata directly:

SELECT column_name, data_type, character_maximum_length AS max_len, is_nullable, column_default
FROM information_schema.columns
WHERE table_schema = 'clinic' AND table_name = 'patients'
ORDER BY ordinal_position;

Result: 9 rows

Find every table in a database that has a given column, handy before renaming it:

SELECT table_name, column_type
FROM information_schema.columns
WHERE table_schema = 'clinic' AND column_name = 'doctor_id';

Result: 2 rows

Count the columns of each table:

SELECT table_name, COUNT(*) AS columns
FROM information_schema.columns
WHERE table_schema = 'clinic'
GROUP BY table_name;

Result: 2 rows

7. SHOW CREATE TABLE: the Complete Definition

SHOW COLUMNS doesn't show which index a column is in, foreign key targets or table options. SHOW CREATE TABLE prints the full statement you could use to recreate the table:

SHOW CREATE TABLE doctors;

Result: 1 row

8. From the Command Line

mysqlshow -u root -p clinic patients        # columns of one table
mysql -u root -p -e "DESCRIBE clinic.patients"   # one-off DESCRIBE

9. Which One Should You Use?

10. Practice Exercises

SHOW COLUMNS FROM patients WHERE `Default` IS NOT NULL;

SELECT column_name, column_key
FROM information_schema.columns
WHERE table_schema = 'clinic' AND table_name = 'patients' AND column_key <> ''
ORDER BY ordinal_position;

11. Summary

• SHOW COLUMNS FROM t;, DESCRIBE t; and DESC t; show name, type, nullability, key, default and extra.
• SHOW FULL COLUMNS adds collation, privileges and comments.
• Filter with LIKE or WHERE.
• information_schema.columns supports searching across tables.
• SHOW CREATE TABLE gives the complete definition.