How to Check a Column’s Data Type in SQL

In SQL, the columns information schema view, which returns information about columns, is an ISO standard view that is supported by most of the major RDBMSs. You can use this view to get information about a column’s data type.

Most of the major RDBMs also provide other methods for getting column information.

Here are examples of getting the data type of a column in various SQL databases.

The `information_schema.columns` View

As mentioned, most major RDBMSs support the information_schema.columns view.

Here’s an example of using that view to return column data type information:

SELECT 
    COLUMN_NAME, 
    DATA_TYPE, 
    CHARACTER_MAXIMUM_LENGTH AS MAX_LENGTH, 
    CHARACTER_OCTET_LENGTH AS OCTET_LENGTH 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'Products' 
AND COLUMN_NAME = 'ProductName';

Replace Products and ProductName with the name of your table and column respectively. Add/reduce the number of columns returned as required.

Most major RDBMSs provide a more concise way to get table and column information. Some of these are listed below.

SQLite

The above doesn’t work in SQLite.

The quickest/easiest way to do it in SQLite is to use the PRAGMA table_info() statement:

PRAGMA table_info(Regions);

Alternatively, you can query the sqlite_schema table. In SQLite, every database contains a sqlite_schema table that stores the schema for that database. Here’s an example of getting information about the Regions table in the Northwind database:

SELECT sql 
FROM Northwind.sqlite_schema 
WHERE tbl_name = 'Regions';

Another way to do it is to use the .schema command:

.schema Regions

See 5 Ways to Check a Column’s Data Type in SQLite for more options.

MySQL

MySQL supports the information_schema.columns view, but you may find it easier to use another method, such as the DESCRIBE statement:

DESCRIBE Pets;

That returns info about a table called Pets.

You can append the column name to narrow it to one column:

DESCRIBE Pets PetName;

That returns info about the PetName columns in the Pets table.

The DESCRIBE statement is actually a shortcut for SHOW COLUMNS FROM. Therefore, you could replace DESCRIBE with SHOW COLUMNS FROM if you prefer.

See 4 Ways to Check a Column’s Data Type in MySQL for more options and examples.

MariaDB

MariaDB is very similar to MySQL, and so we can also use the DESCRIBE statement with MariaDB.

And as with MySQL, DESCRIBE is a shortcut for SHOW COLUMNS FROM.

So, here’s an example of using that syntax

SHOW COLUMNS FROM Pets
WHERE Field = 'PetName';

As you can see, the DESCRIBE syntax is much more concise, but at least we know our options.

I should also mention that MariaDB also supports the information_schema.columns view.

See 4 Ways to Check a Column’s Data Type in MariaDB for more options.

SQL Server

SQL Server also supports the information_schema.columns view, but it also provides a few other options.

One quick option is to use the sp_help stored procedure:

EXEC sp_help Products;

This returns a lot of information about the specified table (in this case, the Products table).

See 3 Ways to Get a Column’s Data Type in SQL Server (T-SQL) for more options and examples.

PostgreSQL

Postgres also supports the information_schema.columns view, but there are also a couple of other options.

If you’re using psql, you can use the \d command to quickly get information about tables, views, materialised views, index, sequences, and foreign tables:

\d public.actor

This returns a lot more than just the data type of a specific column, but at least it’s an option.

See 3 Ways to Check a Column’s Data Type in PostgreSQL for more.

Oracle

In Oracle, we can use the DESC command:

DESC HR.COUNTRIES;

That gets information about the COUNTRIES table, owned by HR.

Or we could query the ALL_TAB_COLUMNS view:

SELECT 
    COLUMN_NAME,
    DATA_TYPE,
    DATA_LENGTH,
    DATA_PRECISION,
    DATA_SCALE
FROM ALL_TAB_COLUMNS
WHERE OWNER = 'HR'
AND TABLE_NAME = 'COUNTRIES';

See 3 Ways to Check Column Data Type in Oracle for more info.

The information_schema.columns View