Describing a MySQL table refers to retrieving its definition or structure. When we describe a table, it basically includes the fields present, their datatypes, and if any constraints defined on them.

We can get the information about the table structure using the following SQL statements −

• DESCRIBE Statement

• DESC Statement

• SHOW COLUMNS Statement

• EXPLAIN Statement

All these statements are used for the same purpose. Let us learn about them in detail, one by one, in this tutorial.

DESCRIBE Statement

The MySQL DESCRIBE statement is used to retrieve a table-related information, which consists of field names, field data types, and constraints (if any). This statement is a shortcut for the SHOW columns statement (they both retrieve the same information from a table).

Apart from retrieving a table”s definition, this statement can be used to get the information of a particular field in a table.

Syntax

Following is the syntax of MySQL DESCRIBE statement −

DESCRIBE table_name [col_name | wild];

Example

In the following example, we are creating a table named CUSTOMERS using the 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, execute the following query to get the information about columns of the CUSTOMERS table −

DESCRIBE CUSTOMERS;

Output

Following is the columns information of CUSTOMERS table −

Describing a specific column

By default, the DESCRIBE statement provides information about all the columns in the specified table. But you can also retrieve information about a particular column of a table by specifying the name of that column.

For example, the following query displays information about NAME column of CUSTOMERS table, which we created in the previous example.

DESCRIBE CUSTOMERS NAME;

Output

Following is the description of NAME column in CUSTOMERS table −

DESC Statement

We can also retrieve the table information using the MySQL DESC statement instead of DESCRIBE. They both give the same results, so DESC is just a shortcut for DESCRIBE statement.

Syntax

Following is the syntax of the MySQL DESC statement −

DESC table_name [col_name | wild];

Example

In this example, we are trying to get the information of CUSTOMERS table using DESC statement.

DESC CUSTOMERS;

Following is the columns information of CUSTOMERS table −

Describing a specific column

We can also get the information of a specific column in a given table, similar to using DESCRIBE. Instead of DESCRIBE, we use DESC.

For example, the following query displays information about NAME column of CUSTOMERS table.

DESC CUSTOMERS NAME;

Output

Following is the description of NAME column in CUSTOMERS table −

SHOW COLUMNS Statement

The MySQL SHOW COLUMNS Statement is used to display the information of all the columns present in a table. The DESCRIBE statement is a shortcut for this statement.

Note: This statement will not display information of a specific field.

Syntax

Following is the syntax of the SHOW COLUMNS statement −

SHOW COLUMNS FROM table_name;

Example

Here, we are retrieving column information of the same CUSTOMERS table using the SHOW COLUMNS statement.

SHOW COLUMNS FROM CUSTOMERS;

Following is the columns information of CUSTOMERS table −

EXPLAIN Statement

The MySQL EXPLAIN Statement is a synonym of DESCRIBE Statement which retrieves the information of a table”s structure such as column names, column data types, and constraints (if any).

Syntax

Following is the syntax of the SHOW COLUMNS statement −

EXPLAIN table_name;

Example

In the following query, we are retrieving column information of the CUSTOMERS table using the EXPLAIN statement.

EXPLAIN CUSTOMERS;

Following is the columns information of CUSTOMERS table −

Describe Tables in Different Formats

You can retrieve the information in various formats using the explain_type option. The value to this option can be TRADITIONAL, JSON and, TREE.

Syntax

Following is the syntax to describe tables in different formats −

{EXPLAIN | DESCRIBE | DESC} 
explain_type: { FORMAT = format_name } select_statement

Example

In the following example, we are describing the CUSTOMERS table format as TRADITIONAL.

EXPLAIN FORMAT = TRADITIONAL SELECT * FROM CUSTOMERS;

Output

Executing the query above will produce the following output −

Example

Here, we are describing the CUSTOMERS table format as JSON.

EXPLAIN FORMAT = JSON SELECT * FROM CUSTOMERS;

Output

Executing the query above will produce the following output −

Example

In the following example, we are describing the CUSTOMERS table format as TREE.

EXPLAIN FORMAT = TREE SELECT * FROM CUSTOMERS;

Output

Executing the query above will produce the following output −

Describing Table Using a Client Program

In addition to describe a table from MySQL Database using the MySQL query, we can also perform the DESCRIBE TABLE operation on a table using a client program.

Syntax

Following are the syntaxes to describe a table from MySQL Database in various programming languages −

$sql="Describe Table_name";
$mysqli->query($sql);

sql="Describe Table_name";
con.query(sql);

String sql="Describe Table_name";
statement.executeUpdate(sql);

sql="Describe Table_name";
cursorObj.execute(sql);

Example

Following are the programs −

$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.
'');

$sql = " DESCRIBE sales ";
if ($q = $mysqli->query($sql)) {
    printf(" Table described successfully.
");
    while ($row = mysqli_fetch_array($q)) {
        echo "{$row[''Field'']} - {$row[''Type'']}n";
    }
}
if ($mysqli->errno) {
    printf("table could not be described .
", $mysqli->error);
}
$mysqli->close();

Table described successfully.
ID - int
ProductName - varchar(255)
CustomerName - varchar(255)
DispatchDate - date
DeliveryTime - time
Price - int
Location - varchar(255)

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 = "USE TUTORIALS"
  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);

  sql = "DESCRIBE SALES;"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log(result);
  });
});

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 DescribeTable {
    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...!");

            //Describe table details...!
            ResultSet resultSet = statement.executeQuery("DESCRIBE customers");
            System.out.println("Following is the table description");
            while(resultSet.next()) {
                System.out.print(resultSet.getNString(1));
                System.out.println();
            }
            connection.close();
        }
        catch(Exception e){
            System.out.println(e);
        }
    }
}

Connected successfully...!
Following is the table description
ID
NAME
AGE
ADDRESS
SALARY

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
table_name = ''tutorials_tbl''
#Creating a cursor object 
cursorObj = connection.cursor()
describe_table_query = f"DESCRIBE {table_name}"
cursorObj.execute(describe_table_query)
columns_info = cursorObj.fetchall()
print(f"Description 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]}, Extra: {column[5]}")
cursorObj.close()
connection.close()

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

user