GUIs like SSMS or Azure Data Studio make it easy to see a column’s data type. Usually it’s a simple matter of navigating to the column in the object explorer and you can see the data type right next to the column.

But if you’re using T-SQL, you’ll need to run a query.

The information_schema.columns View

The information_schema.columns view is a good option if you simply want the data type and no more:

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';

Sample result:

+---------------+-------------+--------------+----------------+
| COLUMN_NAME   | DATA_TYPE   | MAX_LENGTH   | OCTET_LENGTH   |
|---------------+-------------+--------------+----------------|
| ProductName   | varchar     | 255          | 255            |
+---------------+-------------+--------------+----------------+

Replace Products and ProductName with the name of your table and column respectively.

OK, I returned a bit more than just the data type here. But you can omit the other columns if required. Or you can add more. For example, there are columns that contain the column’s precision in case you’re looking at a numeric or datetime column.

You can return all columns like this:

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'Products' 
AND COLUMN_NAME = 'ProductName';

The sys.columns View

The sys.columns view is another option. We can join this with the sys.tables view to get a specific column from a specific table:

SELECT 
    c.name,
    type_name(c.system_type_id) AS system_type,
    type_name(c.user_type_id) AS user_type,
    c.max_length,
    c.precision,
    c.scale
FROM sys.tables t 
JOIN sys.columns c ON t.object_id = c.object_id
WHERE t.name = 'Products'
AND c.name = 'ProductName';

Sample result:

+-------------+---------------+-------------+--------------+-------------+---------+
| name        | system_type   | user_type   | max_length   | precision   | scale   |
|-------------+---------------+-------------+--------------+-------------+---------|
| ProductName | varchar       | varchar     | 255          | 0           | 0       |
+-------------+---------------+-------------+--------------+-------------+---------+

Again, include more or less columns as required.

In this example, I used the TYPE_NAME() function to return the name of the data type, based on its ID. This saved me from having to do a join on the sys.types table.

The sp_help Stored Procedure

The sp_help stored procedure can be useful if you want to return more information about the table.

This stored procedure returns  information about a database object (any object listed in the sys.sysobjects compatibility view), a user-defined data type, or a data type:

EXEC sp_help Products;

This returns a lot of output, so I won’t list it all here.

Just replace Products with the name of the table or other object you want to get information about.

Get a Column’s Data Type from a Query

You can also get the data type of the columns returned by a query.

See Find Out the Data Type of the Columns Returned in a Result Set in SQL Server for more information and examples.