How to get information about the table?

Q: How to get information about the table?

SHOW COLUMNS FROM CUSTOMERS; SHOW COLUMNS IN CUSTOMERS; SHOW FIELDS IN CUSTOMERS; SHOW COLUMNS FROM CUSTOMERS LIKE 'N%'; SHOW COLUMNS FROM CUSTOMERS WHERE Type= 'int'; SHOW COLUMNS IN CUSTOMERS FROM TUTORIALS; SHOW FULL COLUMNS IN CUSTOMERS FROM tutorials;

Difficulty: 2/10

metadata queries, information_schema, table inspection

To retrieve entire information of a table, we use DESCRIBE, DESC or SHOW COLUMNS statements.

Syntax:

Filtering columns to be shown:

We can specify the name of the database along with the table name as shown in the query below

Usually, the information provided by the SHOW COLUMNS statements contains field type, can be null or not, key, default values and some extra details. If you use the full clause details like collation, privileges and comments will be added.

Scenario Questions

0-2 years experience

1You're about to write an INSERT for a `users` table but aren't sure of the exact column names and types. What command do you run to check?
2A teammate claims the `email` column is VARCHAR(255) but you think it might be TEXT. How do you verify who's right without asking anyone else?
3You're reviewing a migration file and need to see the exact CREATE TABLE statement for the current `orders` table. What's the quickest way to get that?

Follow-up Questions

What's the practical difference between DESCRIBE and SHOW COLUMNS?
When would you choose INFORMATION_SCHEMA over SHOW commands?
How do you get foreign key definitions for a table?