Create Tables Show Tables Alter Tables Rename Tables Clone Tables Truncate Tables Temporary Tables Repair Tables Describe Tables Add/Delete Columns Show Columns Rename Columns Table Locking Drop Tables Derived Tables

Queries Constraints Insert Query Select Query Update Query Delete Query Replace Query Insert Ignore Insert on Duplicate Key Update Insert Into Select

Create Views Update Views Drop Views Rename Views

Indexes Create Index Drop Index Show Indexes Unique Index Clustered Index Non-Clustered Index

Using Joins Inner Join Left Join Right Join Cross Join Full Join Self Join Delete Join Update Join Union vs Join

Unique Key Primary Key Foreign Key Composite Key Alternate Key

Triggers Create Trigger Show Trigger Drop Trigger Before Insert Trigger After Insert Trigger Before Update Trigger After Update Trigger Before Delete Trigger After Delete Trigger

Data Types VARCHAR BOOLEAN ENUM DECIMAL INT FLOAT BIT TINYINT BLOB SET

Regular Expressions RLIKE Operator NOT LIKE Operator NOT REGEXP Operator regexp_instr() Function regexp_like() Function regexp_replace() Function regexp_substr() Function

Fulltext Search Natural Language Fulltext Search Boolean Fulltext Search Query Expansion Fulltext Search ngram Fulltext Parser

Date and Time Functions Arithmetic Operators Numeric Functions String Functions Aggregate Functions

Useful Functions Statements Reference Quick Guide Useful Resources Discussion

Feedback

Submit request if you have any questions.

Course

Show Columns

MySQL Tutorial

This SQL tutorial is structured for beginners to guide them from the foundational concepts to advanced data manipulation and querying techniques in SQL. By the end of this tutorial, you will have developed a robust understanding of SQL, equipping you with the knowledge to efficiently manage and analyze data across various database systems. This guide sets the stage for your growth into a skilled data professional, ready to tackle complex data challenges and contribute to the field of data analysis and database management.

Show Columns
MySQL Show Columns Statement
To retrieve entire information of a table, we use DESCRIBE, DESC or SHOW COLUMNS statements.
All of these statements of MySQL can be used to retrieve/display the description of all the columns of a table, as they all retrieve the same result-sets.
Obtaining column information can be useful in several situations like inserting values into a table (based on the column datatype), updating or dropping a column, or to just simply know a table's structure.
In this chapter, let us understand how to use SHOW COLUMNS statement in detail.
Syntax
Following is the syntax of the MySQL SHOW COLUMNS Statement 
SHOW [EXTENDED] [FULL] {COLUMNS | FIELDS}
   {FROM | IN} tbl_name
   [{FROM | IN} db_name]
   [LIKE 'pattern' | WHERE expr]
Example
Let us start with creating a database named TUTORIALS using the below query 
CREATE DATABASE TUTORIALS;
Execute the following statement to change into TUTORIALS database 
USE TUTORIALS;
In the following query, we are creating a table named CUSTOMERS using the following CREATE TABLE statement 
CREATE TABLE CUSTOMERS (
   ID INT AUTO_INCREMENT,
   NAME VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);
Now, we are using the SHOW COLUMNS statement to retrieve the information about columns of the CUSTOMERS table 
SHOW COLUMNS FROM CUSTOMERS;
Output
Following is the information of columns in CUSTOMERS table 
Field
Type
Null
Key
Default
Extra
ID
int
NO
PRI
NULL
auto_increment
NAME
varchar(20)
NO
﻿
NULL
﻿
AGE
int
NO
﻿
NULL
﻿
ADDRESS
char(25)
YES
﻿
NULL
﻿
SALARY
decimal(18,2)
YES
﻿
NULL
﻿
Example
We can also use the IN clause instead of FROM as shown in the query below 
SHOW COLUMNS IN CUSTOMERS;
Output
As we can obeserve the output, it is exactly the same as the previous output.
Field
Type
Null
Key
Default
Extra
ID
int
NO
PRI
NULL
auto_increment
NAME
varchar(20)
NO
﻿
NULL
﻿
AGE
int
NO
﻿
NULL
﻿
ADDRESS
char(25)
YES
﻿
NULL
﻿
SALARY
decimal(18,2)
YES
﻿
NULL
﻿
Example
We can specify the name of the database along with the table name as shown in the query below 
SHOW COLUMNS IN CUSTOMERS FROM TUTORIALS;
Output
Following is the information of columns in CUSTOMERS table that is present in TUTORIALS database.
Field
Type
Null
Key
Default
Extra
ID
int
NO
PRI
NULL
auto_increment
NAME
varchar(20)
NO
﻿
NULL
﻿
AGE
int
NO
﻿
NULL
﻿
ADDRESS
char(25)
YES
﻿
NULL
﻿
SALARY
decimal(18,2)
YES
﻿
NULL
﻿
Example
We can replace the COLUMNS clause with FIELDS and get the same results 
SHOW FIELDS IN CUSTOMERS;
Output
As we see the output, we got the same results as COLUMNS clause.
Field
Type
Null
Key
Default
Extra
ID
int
NO
PRI
NULL
auto_increment
NAME
varchar(20)
NO
﻿
NULL
﻿
AGE
int
NO
﻿
NULL
﻿
ADDRESS
char(25)
YES
﻿
NULL
﻿
SALARY
decimal(18,2)
YES
﻿
NULL
﻿
The LIKE clause
In MySQL, using the LIKE clause, you can specify a pattern to retrieve info about specific columns.
Example
Following query retrieves the column names starting with the letter "P" from CUSTOMERS table.
SHOW COLUMNS FROM CUSTOMERS LIKE 'N%';
Output
Executing the query above will produce the following output 
Field
Type
Null
Key
Default
Extra
NAME
varchar(20)
NO
﻿
NULL
﻿
The WHERE clause
We can use the MySQL WHERE clause of the SHOW COLUMNS statements to retrieve information about the columns which match the specified condition.
Example
In the following example, we are using the WHERE clause to retrieve the columns where there type is int.
SHOW COLUMNS FROM CUSTOMERS WHERE Type= 'int';
Output
Executing the query above will produce the following output 
Field
Type
Null
Key
Default
Extra
ID
int
NO
PRI
NULL
auto_increment
AGE
int
NO
﻿
NULL
﻿
The FULL clause
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.
Example
In the following example, we are using the FULL clause with SHOW COLUMNS to retrieve extra details of the CUSTOMERS table 
SHOW FULL COLUMNS IN CUSTOMERS FROM tutorials;
Executing the query above will produce the following output 
Field
Type
Collation
Null
Key
Default
ID
int
NULL
NO
PRI
NULL
NAME
varchar(20)
utf8mb4 0900 ai ci
NO
﻿
NULL
AGE
int
NULL
NO
﻿
NULL
ADDRESS
char(25)
utf8mb4 0900 ai ci
YES
﻿
NULL
SALARY
decimal(18,2)
NULL
YES
﻿
NULL
Showing Columns of a table Using a Client Program
Besides showing the columns of a table in a MySQL database with a MySQL query, we can also use a client program to perform the SHOW COLUMNS operation.
Syntax
Following are the syntaxes to show columns of a MySQL table in various programming languages 
PHPNodeJSJavaPython
To show columns of a MySQL table through a PHP program, we need to execute the Show Columns statement using the mysqli function query() as 
$sql="Show Table_name";
$mysqli->query($sql);
Example
Following are the programs 
PHPNodeJSJavaPython
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$dbname = 'TUTORIALS';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
﻿
if ($mysqli->connect_errno) {
    printf("Connect failed: %s", $mysqli->connect_error);
    exit();
}
// printf('Connected successfully.');
﻿
//column can be shown by the following queries
// $sql = "SHOW COLUMNS FROM tut_tbl";
$sql = "SHOW COLUMNS FROM sales FROM tutorials";
if ($show_clmn = $mysqli->query($sql)) {
    printf("show column executed successfully!.");
    while ($col = mysqli_fetch_array($show_clmn)) {
        echo "\n{$col['Field']}";
    }
}
if ($mysqli->errno) {
    printf("Columns could be shown by the above query!.", $mysqli->error);
}
$mysqli->close();
Output
The output obtained is as follows 
show column executed successfully!.
ID
ProductName
CustomerName
DispatchDate
DeliveryTime
Price
Location
S
﻿
﻿
﻿
﻿


﻿

Field	Type	Null	Key	Default	Extra
ID	int	NO	PRI	NULL	auto_increment
NAME	varchar(20)	NO		NULL
AGE	int	NO		NULL
ADDRESS	char(25)	YES		NULL
SALARY	decimal(18,2)	YES		NULL

MySQL Basics

MySQL Databases

MySQL Users

MySQL Tables

MySQL Queries

MySQL Views

MySQL Indexes

MySQL Operators and Clauses

MySQL Joins

MySQL Keys

MySQL Triggers

MySQL Data Types

MySQL Regular Expressions

MySQL Fulltext Search

MySQL Functions & Operators

MySQL Misc Concepts

MySQL Useful Resources

MySQL Tutorial

Show Columns

MySQL Show Columns Statement

Syntax

Example

Output

Example

Output

Example

Output

Example

Output

The LIKE clause

Example

Output

The WHERE clause

Example

Output

The FULL clause

Example

Showing Columns of a table Using a Client Program

Syntax

Example

Output