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
08Step-by-step execution
- 1.SHOW TABLES reads the data dictionary and lists departments and employees.
- 2.DESCRIBE employees returns one row per column: emp_id (PRI, NOT NULL), name (NOT NULL), dept_id, salary, hire_date.
- 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
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
| Command | Shows | Standard SQL alternative |
|---|---|---|
| SHOW TABLES | Table names | information_schema.TABLES |
| DESCRIBE t | Columns, types, keys | information_schema.COLUMNS |
| SHOW CREATE TABLE t | Full DDL | — (tool-specific) |
| SHOW INDEX FROM t | Indexes | information_schema.STATISTICS |
14Practical exercises
Beginner1. List all tables.
Reveal solution (15)
Tables in the current database.
Beginner2. Show the columns of departments.
Reveal solution (15)
DESC departments; works too.
Beginner3. List only tables whose name starts with 'dep'.
Reveal solution (15)
% matches any characters.
Intermediate4. Print the DDL of employees.
Reveal solution (15)
Copy-paste ready CREATE statement.
Intermediate5. Add an index on salary and confirm it exists.
Reveal solution (15)
SHOW INDEX lists idx_salary.
Intermediate6. Add a column email, then use SHOW COLUMNS to verify it.
Reveal solution (15)
The new column appears at the end.
Advanced7. Create a copy of employees with CTAS and compare both structures.
Reveal solution (15)
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)
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.
Lesson test
Take an 8-question test on “DESCRIBE & SHOW – Inspecting Structure” — concepts, output prediction, bug spotting and interview questions.