Describe all tables in the database with a single statement?

Unveiling Your Database Schema: A Single-Statement Approach

Learn how to quickly and efficiently describe all tables within your MySQL database using simple SQL queries, understanding their structure and relationships.

Understanding the structure of your database is fundamental for development, debugging, and maintenance. While graphical tools provide a visual overview, sometimes you need to quickly extract schema information directly from the database using SQL. This article focuses on how to describe all tables in a MySQL database with a single, concise statement, providing insights into their columns, data types, and other attributes.

The INFORMATION_SCHEMA Database

MySQL, like many other relational database management systems, provides a special database called INFORMATION_SCHEMA. This database acts as a data dictionary, containing metadata about all other databases, tables, columns, privileges, and more. It's an invaluable resource for querying schema information programmatically.

SELECT
    TABLE_SCHEMA,
    TABLE_NAME,
    COLUMN_NAME,
    ORDINAL_POSITION,
    COLUMN_TYPE,
    IS_NULLABLE,
    COLUMN_KEY,
    COLUMN_DEFAULT,
    EXTRA
FROM
    INFORMATION_SCHEMA.COLUMNS
WHERE
    TABLE_SCHEMA = 'your_database_name'
ORDER BY
    TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION;

Comprehensive query to describe all columns for tables in a specific database.

This powerful query directly accesses the COLUMNS table within INFORMATION_SCHEMA. Let's break down what each selected column provides:

erDiagram
    "INFORMATION_SCHEMA.COLUMNS" {
        VARCHAR TABLE_SCHEMA PK
        VARCHAR TABLE_NAME PK
        VARCHAR COLUMN_NAME PK
        INT ORDINAL_POSITION
        VARCHAR COLUMN_TYPE
        VARCHAR IS_NULLABLE
        VARCHAR COLUMN_KEY
        VARCHAR COLUMN_DEFAULT
        VARCHAR EXTRA
    }
    "INFORMATION_SCHEMA.TABLES" {
        VARCHAR TABLE_SCHEMA PK
        VARCHAR TABLE_NAME PK
        VARCHAR TABLE_TYPE
        DATETIME CREATE_TIME
        BIGINT AVG_ROW_LENGTH
    }
    "INFORMATION_SCHEMA.COLUMNS" ||--o{ "INFORMATION_SCHEMA.TABLES" : "contains" 

Entity-Relationship diagram showing the relationship between COLUMNS and TABLES in INFORMATION_SCHEMA.

Understanding the Output Columns

The output of the query provides detailed information about each column in every table within the specified database. Here's a brief explanation of the key fields:

1. TABLE_SCHEMA

The name of the database (schema) to which the table belongs.

2. TABLE_NAME

The name of the table containing the column.

3. COLUMN_NAME

The name of the column itself.

4. ORDINAL_POSITION

The physical position of the column within the table (starting from 1).

5. COLUMN_TYPE

The full data type definition of the column (e.g., VARCHAR(255), INT(11), DATETIME).

6. IS_NULLABLE

Indicates whether the column can contain NULL values (YES or NO).

7. COLUMN_KEY

Specifies if the column is part of a key (PRI for Primary Key, UNI for Unique Key, MUL for non-unique index).

8. COLUMN_DEFAULT

The default value assigned to the column if no value is explicitly provided during insertion.

9. EXTRA

Additional information, such as auto_increment for auto-incrementing columns.

Alternative: Listing Tables Only

If you only need a list of table names without detailed column information, you can query the TABLES table within INFORMATION_SCHEMA or use the simpler SHOW TABLES statement.

INFORMATION_SCHEMA.TABLES

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'your_database_name';

SHOW TABLES

USE your_database_name; SHOW TABLES;