Sql statement to get column type

sql

sql-queries

database-schema

data-types

byAlex Kataev·Mar 7, 2025

Locate a column's data type in SQL with this simple snippet:

SELECT DATA_TYPE 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'MyTable' AND COLUMN_NAME = 'MyColumn';

Replace MyTable and MyColumn with your specific table and column names. This code returns the data type promptly. Works perfectly on SQL Server, MySQL, PostgreSQL.

Further inspection of your database schema

Dive deeper and unfold the nuances of your SQL schema. Go beyond just getting a column's data type, round out your understanding, and take control of your data!

It's all about the details

To see more than just the column data type, include these attribute-powerhouses:

-- "SQL schema, cough up the details!"
SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE, IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'YourTable';

Here, CHARACTER_MAXIMUM_LENGTH handles the size for character types, NUMERIC_PRECISION and NUMERIC_SCALE manage the digits for numerical types, and IS_NULLABLE shows if a column allows NULLs.

The art of SQL defaults and nuances

SQL Server always has a few tricks up its sleeve. For instance, nvarchar without explicit size defaults to nvarchar(1)—a codename for "I'm lonely." Similarly, float(n) gets a new identity, real when n ≤ 24 and float otherwise.

The elaborate type descriptors

Use SQL's CASE construct to stitch together your column's full definition. Assemble a complete column profile that includes everything:

-- "I'm more than just a column. Look into my character!"
SELECT COLUMN_NAME, 
       CASE 
           WHEN DATA_TYPE = 'nvarchar' AND CHARACTER_MAXIMUM_LENGTH = -1 THEN 'nvarchar(MAX)'
           WHEN DATA_TYPE = 'nvarchar' THEN CONCAT('nvarchar(', CHARACTER_MAXIMUM_LENGTH, ')')
           WHEN DATA_TYPE = 'float' THEN 'float(53)'
           ELSE DATA_TYPE 
       END AS DetailedDataType
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'YourTable';

Inside SQL Server's toolbox

With SQL Server, you have many system catalogs and procedures at your disposal. Use them wisely:

-- "Mirror, mirror on the wall, who's the fairest table of them all?"
EXEC sp_help 'YourTable'; -- Paints a complete portrait of 'YourTable'

The costume party of SQL types

SQL types across dialects have pseudonyms:

PostgreSQL's real is float4 in disguise.
MySQL's BOOL masquerades as TINYINT(1).

The appropriate column data type

The CONTEXT of the table could affect the interpretation of the column data type. Money matters warrant exact numeric types such as DECIMAL or NUMERIC, whereas application logs might be fine with REAL or DOUBLE PRECISION.