MySQL - Show Columns

MySQL: Describing Table Structure

Understanding the structure of a table—its columns, data types, keys, and other properties—is a fundamental task for any database developer or administrator. MySQL provides several ways to retrieve this metadata, from simple commands to a powerful, standardized information schema.

The SHOW COLUMNS and DESCRIBE Statements

SHOW COLUMNS and DESCRIBE (or its shorthand, DESC) are simple, MySQL-specific statements that provide a human-readable summary of a table’s structure. They are functionally identical.

Syntax

The syntax is straightforward:

SHOW COLUMNS {FROM | IN} table_name [{FROM | IN} db_name];

-- Or using DESCRIBE
DESCRIBE table_name;

Example

Let’s first create a sample CUSTOMERS table in a database named TUTORIALS.

USE TUTORIALS;

CREATE TABLE CUSTOMERS(
    ID INT AUTO_INCREMENT PRIMARY KEY,
    NAME VARCHAR(100) NOT NULL,
    AGE INT NOT NULL,
    ADDRESS VARCHAR(255) NULL,
    SALARY DECIMAL(10, 2) NULL
);

Now, let’s use SHOW COLUMNS to inspect its structure.

SHOW COLUMNS FROM CUSTOMERS;

Output

The output provides a clear overview of each column.

Field	Type	Null	Key	Default	Extra
ID	int	NO	PRI	NULL	auto_increment
NAME	varchar(100)	NO		NULL
AGE	int	NO		NULL
ADDRESS	varchar(255)	YES		NULL
SALARY	decimal(10,2)	YES		NULL

Using DESCRIBE CUSTOMERS; would yield the exact same result.

Filtering the Results

SHOW COLUMNS supports optional LIKE and WHERE clauses to filter the results, which is useful for large tables.

Using the LIKE Clause

To find columns whose names start with ‘A’:

SHOW COLUMNS FROM CUSTOMERS LIKE 'A%';

Using the WHERE Clause

To find all columns that have an int data type:

SHOW COLUMNS FROM CUSTOMERS WHERE Type = 'int';

Best Practice: The INFORMATION_SCHEMA

For programmatic access and more detailed, standardized information, the best practice is to query the INFORMATION_SCHEMA. This is a virtual database available in MySQL (and other SQL databases) that provides access to database metadata. The COLUMNS table within INFORMATION_SCHEMA is particularly useful.

Example: Querying INFORMATION_SCHEMA

This query retrieves more detailed information and is more flexible than SHOW COLUMNS.

SELECT
    COLUMN_NAME,
    COLUMN_TYPE,
    IS_NULLABLE,
    COLUMN_KEY,
    COLUMN_DEFAULT,
    EXTRA,
    CHARACTER_SET_NAME,
    COLLATION_NAME
FROM
    INFORMATION_SCHEMA.COLUMNS
WHERE
    TABLE_SCHEMA = 'TUTORIALS'
    AND TABLE_NAME = 'CUSTOMERS'
ORDER BY
    ORDINAL_POSITION;

This approach is preferred in applications because it returns a standard result set that is easy to parse, whereas the output of SHOW COLUMNS can sometimes vary between MySQL versions.

Retrieving Column Information in a Client Program

Here’s how to fetch table schema information in different programming languages, demonstrating both the simple DESCRIBE command and the more robust INFORMATION_SCHEMA query.

Python
Node.js
Java
PHP



This Python example uses `mysql-connector-python` and queries `INFORMATION_SCHEMA` for structured data.

import mysql.connector

DB_CONFIG = {
    'host': 'localhost',
    'user': 'root',
    'password': 'your_password',
    'database': 'TUTORIALS'
}
TABLE_NAME = 'CUSTOMERS'

def describe_table_columns():
    query = """
        SELECT COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_SCHEMA = %s AND TABLE_NAME = %s
        ORDER BY ORDINAL_POSITION
    """
    try:
        with mysql.connector.connect(**DB_CONFIG) as cnx:
            with cnx.cursor(dictionary=True) as cursor:
                cursor.execute(query, (DB_CONFIG['database'], TABLE_NAME))
                columns = cursor.fetchall()
                print(f"Columns for table '{TABLE_NAME}':")
                for col in columns:
                    print(
                        f"- {col['COLUMN_NAME']} ({col['COLUMN_TYPE']}), "
                        f"Nullable: {col['IS_NULLABLE']}"
                    )
    except mysql.connector.Error as err:
        print(f"Database error: {err}")

describe_table_columns()

Output

Columns for table 'CUSTOMERS':
- ID (int), Nullable: NO
- NAME (varchar(100)), Nullable: NO
- AGE (int), Nullable: NO
- ADDRESS (varchar(255)), Nullable: YES
- SALARY (decimal(10,2)), Nullable: YES



This Node.js example uses `mysql2/promise` and prepared statements to query `INFORMATION_SCHEMA`.

const mysql = require('mysql2/promise');

const DB_CONFIG = {
    host: 'localhost',
    user: 'root',
    password: 'your_password',
    database: 'TUTORIALS'
};
const TABLE_NAME = 'CUSTOMERS';

async function describeTable() {
    let connection;
    const query = `
        SELECT COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?
        ORDER BY ORDINAL_POSITION;`;
    try {
        connection = await mysql.createConnection(DB_CONFIG);
        const [columns] = await connection.execute(query, [DB_CONFIG.database, TABLE_NAME]);
        console.log(`Columns for table '${TABLE_NAME}':`);
        columns.forEach(col => {
            console.log(
                `- ${col.COLUMN_NAME} (${col.COLUMN_TYPE}), ` +
                `Nullable: ${col.IS_NULLABLE}`
            );
        });
    } catch (error) {
        console.error(`Failed to get table description: ${error.message}`);
    } finally {
        if (connection) await connection.end();
    }
}

describeTable();

Output

Columns for table 'CUSTOMERS':
- ID (int), Nullable: NO
- NAME (varchar(100)), Nullable: NO
- AGE (int), Nullable: NO
- ADDRESS (varchar(255)), Nullable: YES
- SALARY (decimal(10,2)), Nullable: YES



This Java example uses JDBC and a `PreparedStatement` for a safe and robust query against `INFORMATION_SCHEMA`.

import java.sql.*;

public class ShowColumns {
    static final String DB_URL = "jdbc:mysql://localhost:3306/TUTORIALS";
    static final String USER = "root";
    static final String PASS = "your_password";
    static final String TABLE_NAME = "CUSTOMERS";

    public static void main(String[] args) {
        String query = "SELECT COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE FROM INFORMATION_SCHEMA.COLUMNS " +
                       "WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ? ORDER BY ORDINAL_POSITION";

        try (Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
             PreparedStatement pstmt = conn.prepareStatement(query)) {

            pstmt.setString(1, "TUTORIALS");
            pstmt.setString(2, TABLE_NAME);

            System.out.println("Columns for table '" + TABLE_NAME + "':");
            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    System.out.printf("- %s (%s), Nullable: %s\n",
                        rs.getString("COLUMN_NAME"),
                        rs.getString("COLUMN_TYPE"),
                        rs.getString("IS_NULLABLE"));
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Output

Columns for table 'CUSTOMERS':
- ID (int), Nullable: NO
- NAME (varchar(100)), Nullable: NO
- AGE (int), Nullable: NO
- ADDRESS (varchar(255)), Nullable: YES
- SALARY (decimal(10,2)), Nullable: YES



This PHP example uses the `mysqli` extension with a prepared statement for security and reliability.

<?php
$db_config = [
    'host' => 'localhost',
    'user' => 'root',
    'pass' => 'your_password',
    'name' => 'TUTORIALS'
];
$table_name = 'CUSTOMERS';

$mysqli = new mysqli($db_config['host'], $db_config['user'], $db_config['pass'], $db_config['name']);
if ($mysqli->connect_error) {
    die("Connection failed: " . $mysqli->connect_error);
}

$query = "SELECT COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE
          FROM INFORMATION_SCHEMA.COLUMNS
          WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?
          ORDER BY ORDINAL_POSITION";

$stmt = $mysqli->prepare($query);
$stmt->bind_param("ss", $db_config['name'], $table_name);
$stmt->execute();
$result = $stmt->get_result();

echo "Columns for table '$table_name':<br>";
while ($col = $result->fetch_assoc()) {
    echo "- " . htmlspecialchars($col['COLUMN_NAME'])
       . " (" . htmlspecialchars($col['COLUMN_TYPE']) . "), "
       . "Nullable: " . htmlspecialchars($col['IS_NULLABLE']) . "<br>";
}

$stmt->close();
$mysqli->close();
?>

Output

Columns for table 'CUSTOMERS':
- ID (int), Nullable: NO
- NAME (varchar(100)), Nullable: NO
- AGE (int), Nullable: NO
- ADDRESS (varchar(255)), Nullable: YES
- SALARY (decimal(10,2)), Nullable: YES