← SQL roadmap

Topic 14 of 52 · Beginner

DESCRIBE & SHOW – Inspecting Structure

Look at tables, columns, indexes and full definitions without reading data.

01DESCRIBE & SHOW – Inspecting Structure

Module 02 – Database & Table Management (DDL)

02What is it?

DESCRIBE table (or DESC, or SHOW COLUMNS FROM table) lists a table's columns with their type, NULL rule, key and default.

SHOW TABLES lists tables in the current database, SHOW CREATE TABLE prints the exact DDL that would recreate the table, and SHOW INDEX FROM table lists its indexes.

03Why do we need it?

Before writing a query against an unfamiliar table you need to know its column names and types. These commands answer that in a second, without scanning data.

SHOW CREATE TABLE is how engineers copy a table definition into a migration script or compare dev vs production.

04Syntax

SHOW TABLES;
SHOW TABLES LIKE 'emp%';
DESCRIBE table_name;          -- same as DESC / SHOW COLUMNS FROM table_name
SHOW CREATE TABLE table_name;
SHOW INDEX FROM table_name;

DESCRIBE/SHOW are MySQL commands. PostgreSQL uses \d table in psql or information_schema; SQL Server uses sp_help. In the browser sandbox output columns are simplified.

05Parameter explanation

LIKE 'pattern'
Filters SHOW TABLES using % and _ wildcards.
Field / Type
Column name and data type in DESCRIBE output.
Null
YES if the column accepts NULL.
Key
PRI = primary key, UNI = unique, MUL = non-unique index.
Default
Value used when an INSERT omits the column.

06Sample database

Every example and exercise below starts from a fresh copy of this data.

CREATE TABLE departments (
  dept_id   INT PRIMARY KEY,
  dept_name VARCHAR(50) NOT NULL
);
CREATE TABLE employees (
  emp_id    INT PRIMARY KEY,
  name      VARCHAR(60) NOT NULL,
  dept_id   INT,
  salary    DECIMAL(10,2),
  hire_date DATE
);
INSERT INTO departments VALUES (10,'Engineering'),(20,'Sales'),(30,'HR');
INSERT INTO employees VALUES
 (1,'Asha',10,85000,'2021-04-12'),
 (2,'Ravi',20,52000,'2022-01-03'),
 (3,'Meena',10,97000,'2019-09-20'),
 (4,'Karan',30,46000,'2023-06-15'),
 (5,'Divya',20,61000,'2020-11-30');

07Basic coding example

Basic example · runs on the real engine

08Step-by-step execution

  1. 1.SHOW TABLES reads the data dictionary and lists departments and employees.
  2. 2.DESCRIBE employees returns one row per column: emp_id (PRI, NOT NULL), name (NOT NULL), dept_id, salary, hire_date.
  3. 3.No table data is read — only metadata.

09Before and after output

Generated live by running the basic example against the sample database.

Running on the SQL engine…

10Intermediate coding example

Intermediate example

11Real-world business scenario

When a pipeline fails with 'Unknown column', the first step is DESCRIBE on the target table to see whether a column was renamed upstream.

Data engineers query information_schema.COLUMNS (the SQL-standard version of DESCRIBE) to auto-generate documentation and detect schema drift between environments.

12Common mistakes

SHOW COLUMNS employees;

Missing FROM/IN keyword.

SHOW COLUMNS FROM employees;

DESCRIBE employee;

Table name typo — the table is employees.

SHOW TABLES; to confirm the name, then DESCRIBE employees;

SELECT * FROM big_table LIMIT 1; -- just to see columns

Works, but scans data and doesn't show types or keys.

DESCRIBE big_table;

13Important comparisons

CommandShowsStandard SQL alternative
SHOW TABLESTable namesinformation_schema.TABLES
DESCRIBE tColumns, types, keysinformation_schema.COLUMNS
SHOW CREATE TABLE tFull DDL— (tool-specific)
SHOW INDEX FROM tIndexesinformation_schema.STATISTICS

14Practical exercises

Beginner1. List all tables.

Reveal solution (15)
Solution · run it

Tables in the current database.

Beginner2. Show the columns of departments.

Reveal solution (15)
Solution · run it

DESC departments; works too.

Beginner3. List only tables whose name starts with 'dep'.

Reveal solution (15)
Solution · run it

% matches any characters.

Intermediate4. Print the DDL of employees.

Reveal solution (15)
Solution · run it

Copy-paste ready CREATE statement.

Intermediate5. Add an index on salary and confirm it exists.

Reveal solution (15)
Solution · run it

SHOW INDEX lists idx_salary.

Intermediate6. Add a column email, then use SHOW COLUMNS to verify it.

Reveal solution (15)
Solution · run it

The new column appears at the end.

Advanced7. Create a copy of employees with CTAS and compare both structures.

Reveal solution (15)
Solution · run it

CTAS copies columns but not the primary key — notice the Key column.

Advanced8. Count the columns of employees using the table-info view.

Reveal solution (15)
Solution · run it

In MySQL: SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_NAME = 'employees'.

16Key takeaways

  • DESCRIBE = DESC = SHOW COLUMNS FROM.
  • SHOW CREATE TABLE gives the exact recreatable DDL.
  • Key column: PRI, UNI, MUL.
  • information_schema is the portable way to inspect metadata.

17Knowledge check

1. Which is NOT equivalent to DESCRIBE employees?

2. In DESCRIBE output, Key = 'PRI' means…

3. The standard-SQL way to list columns is…

Interview questions

How do you find all tables that contain a column named customer_id?

SELECT TABLE_NAME FROM information_schema.COLUMNS WHERE COLUMN_NAME = 'customer_id' AND TABLE_SCHEMA = DATABASE();

What does MUL mean in DESCRIBE?

The column is the first column of a non-unique index, so multiple rows may share a value.

Practise SQL with the AI mentor

Lesson test

Take an 8-question test on “DESCRIBE & SHOW – Inspecting Structure” — concepts, output prediction, bug spotting and interview questions.

Can't find a SQL topic you need for exams or placements? Request it and we'll add it.