How to Get the Data Type of Columns in SQL Server

You can use the following query to get the data type of your columns in SQL Server:

SELECT 
TABLE_CATALOG,
TABLE_SCHEMA,
TABLE_NAME, 
COLUMN_NAME, 
DATA_TYPE 
FROM INFORMATION_SCHEMA.COLUMNS

Next, you’ll see a simple example to better understand how to apply the above query across different scenarios.

The Example

In the example below, we’ll select a database called TestDatabase:

Database example

The TestDatabase contains the following 3 tables:

Tables examples

3 Scenarios to Get the Data Type of Columns in SQL Server

Scenario 1 – get the data type of all columns in a particular database:

The TABLE_CATALOG field in the query below represents your database. In our example, the database name is TestDatabase.

In order to get the data type of all the columns in the TestDatabase, simply add where TABLE_CATALOG = ‘TestDatabase’ at the bottom of the query:

SELECT 
TABLE_CATALOG,
TABLE_SCHEMA,
TABLE_NAME, 
COLUMN_NAME, 
DATA_TYPE 
FROM INFORMATION_SCHEMA.COLUMNS
where TABLE_CATALOG = 'TestDatabase' 

As you can see, there are 3 tables in the TestDatabase, and the data type of each column is captured under the DATA_TYPE field:

How to Get the Data Type of Columns in SQL Server

Scenario 2 – get the data type of all columns in a particular table:

Let’s say that you want to find the data type of the columns under the ‘People‘ table. In that case, add where TABLE_NAME = ‘People’ at the bottom of the query:

SELECT 
TABLE_CATALOG,
TABLE_SCHEMA,
TABLE_NAME, 
COLUMN_NAME, 
DATA_TYPE 
FROM INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = 'People' 

There are 3 columns in the ‘People’ table. For the first two columns (Name and City) the data type is nvarchar, while the data type for the last column (Birthday) is date:

Get the Data Type of Columns in SQL Server

Scenario 3 – get the data type for a specific column

Lastly, let’s get the data type for the Name column (under the People table):

SELECT 
TABLE_CATALOG,
TABLE_SCHEMA,
TABLE_NAME, 
COLUMN_NAME, 
DATA_TYPE 
FROM INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = 'People' 
and COLUMN_NAME = 'Name'

As you can see, the data type for the Name column is nvarchar:

How to Get the Data Type of Columns in SQL Server

Optionally, you can also include the CHARACTER_MAXIMUM_LENGTH field in the query:

SELECT 
TABLE_CATALOG,
TABLE_SCHEMA,
TABLE_NAME, 
COLUMN_NAME, 
DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = 'People' 
and COLUMN_NAME = 'Name'

In this case, the CHARACTER_MAXIMUM_LENGTH is 50:

CHARACTER_MAXIMUM_LENGTH