MariaDB Show Column Data Type

This MariaDB tutorial will go through how to Show Column Data Type. To assist you in better understanding the topic, we will explore and draw conclusions from a number of examples in this section by following the below topics.

• How to use MariaDB Show Column Data Type or Data Type From Table
• MariaDB Show Column Data Type in SQL
• How to Show Column Data Type From Database
• MariaDB Show Column Data Type From Database Where Not Null
• How to Show Column Data Type From Database With Default Value

MariaDB Show Column Data Type or Data Type From Table

To view details about the column in the given table with the data type in MariaDB, use the SHOW COLUMNS statement.

The syntax is given below.

SHOW  COLUMNS FROM table_name
                           OR
SHOW  COLUMNS FROM database_name.table_name

SHOW COLUMNS FROM table_name: The statement SHOW COLUMNS shows details about the columns in a specific table (table_name).

Let’s take an example by following the below steps:

Use the below statement to show the column details of the table STATES_OF_USA from the database AIRBNB_DB.

SHOW COLUMNS FROM AIRBNB_DB.STATES_OF_USA;

The above output shows that the data type of each column in the table STATES_OF_USA such as STATE_SHORTFORM and STATE_NAME has a data type of varchar, also the column STATE_ID has a data type of integer or int.

The MariaDB SHOW COLUMNS statement on the table should have helped you understand the subtopic “MariaDB Show Column Data Type”. We have used an example and gone into great detail to provide a better understanding.

Read: MariaDB vs Postgres

MariaDB Show Column Data Type From Database

We have already learned about how to show column data type from the table in the above subsection, now in this section, we will understand how to show column data type from the specific database.

The syntax is given below.

SHOW  COLUMNS FROM table_name FROM database_name

SHOW COLUMNS FROM table_name FROM database_name: The statement SHOW COLUMNS shows details about the columns in a specific table (table_name) of a particular database (database_name).

Let’s take an example by following the below steps:

Use the below statement to show the column details of the table customers from the database classiccars.

SHOW COLUMNS FROM customers FROM classiccars;

The above output shows the data type of the columns from the database directly, as the column customerNumber of type int and customerName of type varchar and so on.

Read: How to create a user in MariaDB

MariaDB Show Column Data Type in SQL

The MariaDB 10.4.x MySQL command-line client also referred to as MariaDB. So in this section, we will use the MySQL command-line client to view the data type of the columns.

Let’s see with an example by following the below steps:

Open the MySQL command-line client and log in with your user id and password. Choose the database

Choose the desired database whose table columns you want to view for the data type.

use classiccars;

Use the statement to view the column’s data type of the table customers.

SHOW COLUMNS FROM customers;

In the above output, the column Type contains the data type of each column in the customer’s table.

Read: How to Show Tables in MariaDB

MariaDB Show Column Data Type From Database With Default Value

We will check in this section how to view the column data type from the database with a default value. When the statement SHOW COLUMNS is executed, then it returns many columns that we have seen in the above subsections. One of the column names is Default which shows the assigned default value of the column.

Let’s see an example and check the column data type with a default value.

Use the below statement to show the column details of the table customers from the database classiccars.

SHOW COLUMNS FROM customers FROM classiccars;

Look at the above output, the column Default shows the default value for the columns of the database classiccars. This is how to show the default values of the columns.

Read: How to Add Column in MariaDB

MariaDB Show Column Data Type From Database Where Not Null

We will learn and comprehend how to use the WHERE condition and the IS NOT NULL condition on the INFORMATION SCHEMA table via the query in this MariaDB section to display column data types from the database.

• Information about the MariaDB server, such as a database or table’s name, a column’s data type, or access privileges, can be accessed using INFORMATION.SCHEMA.

Let’s take an example by following the below steps:

SELECT 
    TABLE_NAME,
	 COLUMN_NAME, 
    DATA_TYPE, 
    CHARACTER_MAXIMUM_LENGTH AS MAX_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = DATABASE()
AND CHARACTER_MAXIMUM_LENGTH IS NOT NULL;

In the above query, the SELECT statement is used to get all records of the columns TABLE_NAME, COLUMN_NAME, DATA_TYPE, and CHARACTER_MAXIMUM_LENGTH from the INFORMATION_SCHEMA.COLUMNS table with the WHERE condition. As shown in the below picture.

In the WHERE condition, the EQUAL TO (=) operator on the DATABASE function is used with the TABLE_NAME to locate the current database, and the IS NOT NULL condition is used with the CHARACTER_MAXIMUM_LENGTH column to locate the NOT NULL value in that column of the INFORMATION_SCHEMA.COLUMNS table.

The SELECT query will obtain all records from the INFORMATION.SCHEMA.COLUMNS table if the WHERE condition is TRUE otherwise, it will do the opposite.

By using the MariaDB SELECT statement on the table, we hope you have grasped the subtopic “MariaDB Show Column Data Type From Database Where Not Null”. To help you understand, we provided a detailed explanation and used an example.

You may also like to read the following MariaDB tutorials.

• Alter Table Add Foreign Key in MariaDB
• Compound Primary Key in MariaDB
• MariaDB Add Column With Default Value
• MariaDB Set Auto Increment Value

We have covered how to show the data type of the columns from the table or database, then checked the null value of some columns using the WHERE condition, and also used different methods like MySQL server and HeidiSQL to show the data type of the columns.

• How to use MariaDB Show Column Data Type or Data Type From Table
• MariaDB Show Column Data Type in SQL
• How to Show Column Data Type From Database
• MariaDB Show Column Data Type From Database Where Not Null
• How to Show Column Data Type From Database With Default Value