MariaDB SHOW COLUMNS

In this article, I will walk you through everything you need to know about the MariaDB SHOW COLUMNS command, from basic syntax to advanced filtering.

MariaDB SHOW COLUMNS

What is the MariaDB SHOW COLUMNS Statement?

At its core, SHOW COLUMNS is an administrative command used to display detailed information about the columns in a specific table or view. It provides a structured look at the “Field” (name), “Type” (data type), “Null” (nullability), “Key” (indexing), and “Default” values.

While many developers use DESCRIBE as a shortcut, SHOW COLUMNS offers a more flexible syntax, especially when you need to filter results or view extended metadata like comments and privileges.

Basic Syntax

The beauty of MariaDB is its flexibility. You can use several variations of the command depending on your current context (i.e., whether you are already “using” a database or calling it from a global scope).

The Essential Syntax

SQL

SHOW [FULL] {COLUMNS | FIELDS}
    FROM tbl_name
    [FROM db_name]
    [LIKE 'pattern' | WHERE expr]

Key Components Explained:

• FULL: An optional keyword. When included, it adds three extra columns: Collation, Privileges, and Comment.
• FIELDS: This is simply a synonym for COLUMNS. You can use them interchangeably.
• FROM/IN: You can use FROM or IN to specify the table.
• LIKE: Allows for simple wildcard matching (e.g., LIKE 'user%').
• WHERE: Allows for more complex filtering using standard SQL expressions.

Understanding the Output: What Do These Columns Mean?

When you run a standard SHOW COLUMNS FROM users; query, you’ll be presented with a table. Here is a breakdown of what each column represents:

Column Name	Description
Field	The name of the column (e.g., user_id, first_name).
Type	The data type of the column (e.g., int(11), varchar(255)).
Null	Whether the column allows NULL values (YES or NO).
Key	Indicates if the column is indexed (PRI, UNI, MUL, or empty).
Default	The default value assigned to the column if none is provided during an insert.
Extra	Additional info, such as auto_increment or on update CURRENT_TIMESTAMP.

Deep Dive into the “Key” Values

Understanding the Key column is crucial for database performance tuning:

• PRI: This column is part of the Primary Key.
• UNI: This column is part of a Unique Index.
• MUL: This stands for “multiple.” It means the column is the first part of a non-unique index where multiple occurrences of a value are allowed.

Example

Here, we will use the same table Person.

SHOW COLUMNS FROM Person;

View the columns of the table Person using the above code. After executing the above query, Igot the expected output as shown in the screenshot below.

Using the FULL Keyword for Advanced Metadata

If you are working on a multi-tenant application, you might have specific collations for different regions or comments explaining why a column exists.

By adding the FULL keyword, you get a much richer data set:

SQL

SHOW FULL COLUMNS FROM employees;

This adds:

1. Collation: Useful for seeing if your varchar fields are using utf8mb4_general_ci or something else.
2. Privileges: Shows exactly what permissions the current user has on that specific column (e.g., select,insert,update).
3. Comment: Displays the text comment you added during table creation. This is a lifesaver for documentation!

Filtering Column Results

Imagine you have a table with 100 columns, but you only care about those related to “billing.” Instead of scrolling through a massive result set, you can filter your output.

Using the LIKE Clause

The LIKE clause is perfect for simple pattern matching.

• To find columns starting with “zip”: SHOW COLUMNS FROM addresses LIKE 'zip%';
• To find columns ending with “code”: SHOW COLUMNS FROM addresses LIKE '%code';

Using the WHERE Clause

The WHERE clause is significantly more powerful because it allows you to filter based on any property returned by the command.

For example, if you want to find every column in your orders table that does not allow NULL values:

SQL

SHOW COLUMNS FROM orders WHERE `Null` = 'NO';

Or perhaps you want to see all columns that use a specific data type:

SQL

SHOW COLUMNS FROM products WHERE Type LIKE 'decimal%';

Common Use Cases and Professional Tips

1. Identifying Auto-Increment Columns

When I’m setting up an ETL (Extract, Transform, Load) process, I always check which column is the auto-incrementing ID to ensure I don’t manually overwrite it.

• Command: SHOW COLUMNS FROM shipments WHERE Extra = 'auto_increment';

2. Checking for Missing Comments

In a professional environment, every column should be documented. You can quickly find “naked” columns that lack documentation.

• Command: SHOW FULL COLUMNS FROM customer_data WHERE Comment = '';

3. Debugging Character Set Issues

If you notice strange symbols appearing in your application, use the FULL keyword to verify that your collations are consistent across your orders and customers tables.

Best Practices

When writing your own database documentation or scripts using these commands, keep these tips in mind:

• Specify the Database: Even if you are already in a database, it’s safer to use SHOW COLUMNS FROM table_name FROM database_name; to avoid ambiguity in complex environments.
• Backticks are Your Friend: If your column names or table names use reserved words, always wrap them in backticks (e.g., `Null`) when using the WHERE clause.
• Avoid Over-Reliance in Code: For application logic, prefer querying INFORMATION_SCHEMA.COLUMNS. It is standard SQL and more portable than the MariaDB-specific SHOW commands.

Summary and Key Takeaways

The MariaDB SHOW COLUMNS provides a human-readable, highly customizable view of your table’s structure.

• Use SHOW COLUMNS for quick inspections.
• Use the FULL keyword to see comments and privileges.
• Leverage WHERE and LIKE to filter through large tables.
• Remember that DESCRIBE is just a shorthand for this powerful command.

Mastering this command helps you maintain a cleaner, better-documented database schema.

You may also like the following articles:

• MariaDB EXPLAIN
• What is MariaDB Database
• MariaDB Drop Table