MySQL – Describe Tables


MySQL – DESCRIBE Tables


Table of content


”;


Previous

Next

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 −




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

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 −



Field Type Null Key Default Extra
NAME varchar(20) NO

NULL

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 −




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

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 −



Field Type Null Key Default Extra
NAME varchar(20) NO

NULL

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 −




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

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 −




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

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 −




id select_type table partitions possible_keys
1 SIMPLE CUSTOMERS NULL NULL

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 −




EXPLAIN

{
“query_block”: {
“select_id”: 1,
“cost_info”: {
“query_cost”: “0.95”
},
“table”: {
“table_name”: “CUSTOMERS”,
“access_type”: “ALL”,
“rows_examined_per_scan”: 7,
“rows_produced_per_join”: 7,
“filtered”: “100.00”,
“cost_info”: {
“read_cost”: “0.25”,
“eval_cost”: “0.70”,
“prefix_cost”: “0.95”,
“data_read_per_join”: “1K”
},
“used_columns”: [
“ID”,
“NAME”,
“AGE”,
“ADDRESS”,
“SALARY”
]
}
}
}

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 −




EXPLAIN
Table scan on CUSTOMERS (cost=0.95 rows=7)


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 −


PHP
NodeJS
Java
Python

To describe a table from MySQL Database through a PHP program, we need to execute the Describe Table statement using the mysqli function query() as −


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

To describe a table from MySQL Database through a Node.js program, we need to execute the Describe Table statement using the query() function of the mysql2 library as −


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

To describe a table from MySQL Database through a Java program, we need to execute the Describe Table statement using the JDBC function executeUpdate() as −


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

To describe a table from MySQL Database through a Python program, we need to execute the Describe Table statement using the execute() function of the MySQL Connector/Python as −


sql="Describe Table_name";
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.
'');

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

Output

The output obtained is as follows −


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

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

Output

The output obtained is as shown below −


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

Output

Following is the output of the above code −


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:



Print Page




Previous



Next


Advertisements

”;

Leave a Reply

Your email address will not be published. Required fields are marked *