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 3 scenarios to get the data type:

  • of all columns in a particular database
  • of all columns in a particular table
  • for a specific column

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

To begin, select your desired database.

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

Database example

The TestDatabase contains the following 3 tables:

Tables examples

You can then use the following query in order to get the data type of all the columns in the TestDatabase:

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

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