How to Determine the Data Type of a Column Using SQL

1. Overview

Understanding the data types of columns in database tables is important. Some of the reasons why it’s important include data validation, query optimization, and data integrity. Generally, SQL provides several methods to determine the data type of a column, which can vary depending on the database management system (DBMS) in use.

In this tutorial, we’ll explore different ways to determine a column’s data type in various SQL-based systems. We’ll cover methods using the information_schema.columns and sys.columns views. Additionally, we’ll cover specific approaches for popular DBMSs.

We’ll provide practical examples using the Baeldung University database scheme. In particular, we’ll use the Student table.

2. The information_schema.columns View

The information_schema.columns view offers a standardized way to retrieve various information on columns in a database table across different SQL-based systems. Among other things, this view contains metadata about the columns, including their data types.

For example, let’s determine the data type of the gpa column in the Student table:

SELECT 
     table_name, 
     column_name, 
     data_type 
 FROM 
     information_schema.columns 
 WHERE 
     table_name = 'student' 
     AND column_name = 'gpa';
+------------+-------------+-----------+
| table_name | column_name | data_type |
|------------+-------------+-----------|
| student    | gpa         | real      |
+------------+-------------+-----------+
SELECT 1
Time: 0.039s

From the output, the data type of the gpa column of the Student table is real.

Additionally, to determine the data type of all columns in the Student table, we can remove the condition on the column_name:

SELECT 
     table_name, 
     column_name, 
     data_type 
 FROM 
     information_schema.columns 
 WHERE 
     table_name = 'student';
+------------+-----------------+-------------------+
| table_name | column_name     | data_type         |
|------------+-----------------+-------------------|
| student    | birth_date      | date              |
| student    | gpa             | real              |
| student    | national_id     | bigint            |
| student    | id              | integer           |
| student    | enrollment_date | date              |
| student    | graduation_date | date              |
| student    | name            | character varying |
+------------+-----------------+-------------------+
SELECT 7
Time: 0.011s

The query returns the data type information for all columns in the Student table.

3. MySQL and MariaDB

MySQL and MariaDB support the information_schema.columns view but also offer simpler commands to determine the data types of columns. These commands are SHOW COLUMNS and DESCRIBE.

3.1. The SHOW COLUMNS Directive

For example, we can use the SHOW COLUMNS to view the data types of all columns in the Student table:

SHOW COLUMNS FROM Student;
+-----------------+-------------+------+-----+---------+-------+
| Field           | Type        | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+-------+
| id              | int         | NO   | PRI | NULL    |       |
| name            | varchar(60) | YES  |     | NULL    |       |
| national_id     | bigint      | NO   |     | NULL    |       |
| birth_date      | date        | YES  |     | NULL    |       |
...

This output shows each column’s name, type, nullability, key information, default value, and any extra information.

3.2. The DESCRIBE Query

We can also use the DESCRIBE command to view the structure of a table, including column data type:

DESCRIBE Student;
+-----------------+-------------+------+-----+---------+-------+
| Field           | Type        | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+-------+
| id              | int         | NO   | PRI | NULL    |       |
| name            | varchar(60) | YES  |     | NULL    |       |
| national_id     | bigint      | NO   |     | NULL    |       |
| birth_date      | date        | YES  |     | NULL    |       |
...

This output provides a detailed view of the table structure, similar to the SHOW COLUMNS command.

Additionally, to get the data of a specific column using DESCRIBE, we can append the column name to the command:

DESCRIBE Student gpa;
+-------+-------+------+-----+---------+-------+
| Field | Type  | Null | Key | Default | Extra |
+-------+-------+------+-----+---------+-------+
| gpa   | float | YES  |     | NULL    |       |
+-------+-------+------+-----+---------+-------+
1 row in set (0.002 sec)

The output displays the data type of the gpa column.

4. SQL Server

SQL Server doesn’t support the information_schema.columns view. Instead, it offers the sys.columns view and the sp_help stored procedure to retrieve this information.

4.1. The sys.columns View

The sys.columns view is a system catalog view that provides information about the columns in a database. It includes metadata about each column, including its name, data type, and other attributes.

For example, we can determine the data type of all columns in the Student table as follows:

SELECT 
    t.name AS table_name,
    c.name AS column_name,
    ty.name AS data_type
FROM 
    sys.columns c
JOIN 
    sys.tables t ON c.object_id = t.object_id
JOIN 
    sys.types ty ON c.user_type_id = ty.user_type_id
WHERE 
    t.name = 'Student';

4.2. The sp_help Stored Procedure

Additionally, the sp_help stored procedure is a quick and easy way to get information about the structure of a table, including column data types. We can use the stored procedure to get the structure of the Student table:

EXEC sp_help 'Student';
Name            Owner        Type          Created_datetime
--------------- ------------ ------------- -------------------
Student         dbo          user table    2024-07-19 00:00:00

Column_name     Type          Computed     Length     Prec  Scale Nullable Default_name     Collation
--------------- ------------- ------------ ---------- ----- ----- -------- ---------------- -----------
id              int           no           4                   0     no     NULL             NULL
name            varchar       no           60                  yes    NULL             SQL_Latin1_General_CP1_CI_AS
national_id     bigint        no           8                   no     NULL             NULL
...

The result displays information about each column, including its name, type, length, precision, scale, and nullability.

5. Conclusion

In this article, we explored various methods to determine the data type of a column in different SQL-based systems.

ANSI SQL provides a general and portable approach through the information_schema.columns view. However, not all DBMSs support it, and many offer additional non-portable custom methods.