MySQL - Show Columns

Table of content

MySQL Show Columns Statement
The LIKE clause
The WHERE clause
The FULL clause
Showing Columns of a table Using a Client Program

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 −

PHP NodeJS Java Python

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);

To show columns of a MySQL table through a Node.js program, we need to execute the Show statement using the query() function of the mysql2 library as −

sql="SHOW COLUMNS FROM table_name";
con.query(sql);

To show columns of a MySQL table through a Java program, we need to execute the Show statement using the JDBC function executeUpdate() as −

String sql="SHOW COLUMNS FROM table_name FROM database";
statement.executeQuery(sql);

To show columns of a MySQL table through a Python program, we need to execute the Show statement using the execute() function of the MySQL Connector/Python as −

sql="SHOW COLUMNS FROM table_name FROM database";
cursorObj.execute(sql);

Example

Following are the programs −

PHP NodeJS Java Python

$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

var mysql = require('mysql2');
var con = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "Nr5a0204@123"
});

  //Connecting to MySQL
  con.connect(function (err) {
  if (err) throw err;
  console.log("Connected!");
  console.log("--------------------------");

  sql = "CREATE DATABASE demo"
  con.query(sql);

  sql = "USE demo"
  con.query(sql);

  sql = "CREATE TABLE sales(ID INT, ProductName VARCHAR(255), CustomerName VARCHAR(255), DispatchDate date, DeliveryTime time, Price INT, Location VARCHAR(255));"
  con.query(sql);

  //Displaying all the columns from the Sales table
  sql = "SHOW COLUMNS FROM sales;"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log(result);
  });
});

Output

The output produced is as follows −

Connected!
--------------------------
[
  {
    Field: 'ID',
    Type: 'int',
    Null: 'YES',
    Key: '',
    Default: null,
    Extra: ''
  },
  {
    Field: 'ProductName',
    Type: 'varchar(255)',
    Null: 'YES',
    Key: '',
    Default: null,
    Extra: ''
  },
  {
    Field: 'CustomerName',
    Type: 'varchar(255)',
    Null: 'YES',
    Key: '',
    Default: null,
    Extra: ''
  },
  {
    Field: 'DispatchDate',
    Type: 'date',
    Null: 'YES',
    Key: '',
    Default: null,
    Extra: ''
  },
  {
    Field: 'DeliveryTime',
    Type: 'time',
    Null: 'YES',
    Key: '',
    Default: null,
    Extra: ''
  },
  {
    Field: 'Price',
    Type: 'int',
    Null: 'YES',
    Key: '',
    Default: null,
    Extra: ''
  },
  {
    Field: 'Location',
    Type: 'varchar(255)',
    Null: 'YES',
    Key: '',
    Default: null,
    Extra: ''
  }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class ShowColumn {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/TUTORIALS";
        String username = "root";
        String password = "password";
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection connection = DriverManager.getConnection(url, username, password);
            Statement statement = connection.createStatement();
            System.out.println("Connected successfully...!");

            //show column
            String sql = "SHOW COLUMNS FROM tutorials_tbl FROM TUTORIALS";
            ResultSet resultSet = statement.executeQuery(sql);
            System.out.println("Column has been shown successfully...!");
            while (resultSet.next()) {
                System.out.print(resultSet.getString(1));
                System.out.println();
            }
            connection.close();
        } catch (Exception e) {
            System.out.println(e);
        }
    }
}

Output

The output obtained is as shown below −

Connected successfully...!
Column has been shown successfully...!
ID
tutorial_title
tutorial_author

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='tut'
)
table_name = 'NOVELS'
#Creating a cursor object 
cursorObj = connection.cursor()
show_columns_query = f"SHOW COLUMNS FROM {table_name}"
cursorObj.execute(show_columns_query)
columns_info = cursorObj.fetchall()
print(f"Columns of table '{table_name}':")
for column in columns_info:
    print(f"Column Name: {column[0]}, Type: {column[1]}, Null: {column[2]}, Key: {column[3]}, Default: {column[4]}")
cursorObj.close()
connection.close()

Output

Following is the output of the above code −

Columns of table 'tutorials_tbl':
Column Name: tutorial_id, Type: b'int', Null: NO, Key: PRI, Default: None
Column Name: tutorial_title, Type: b'varchar(100)', Null: NO, Key: , Default: None
Column Name: tutorial_author, Type: b'varchar(40)', Null: NO, Key: , Default: None
Column Name: submission_date, Type: b'date', Null: YES, Key:, Default: None
Column Name: tutorial_name, Type: b'varchar(20)', Null: YES, Key: , Default: None