You can use the following query to get the data type of 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.
For example, assume that you have a database called the ‘test_database‘ which includes 3 tables.
You can then run the following query in order to get the data type of all the columns in the test_database:
SELECT
TABLE_CATALOG,
TABLE_SCHEMA,
TABLE_NAME,
COLUMN_NAME,
DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
Here is an example of the results that you may get, where the data type of each column is captured under the DATA_TYPE field:
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | DATA_TYPE |
| test_database | dbo | cars | brand | nvarchar |
| test_database | dbo | cars | price | int |
| test_database | dbo | people | first_name | nvarchar |
| test_database | dbo | people | last_name | nvarchar |
| test_database | dbo | people | age | int |
| test_database | dbo | pets | name | nvarchar |
| test_database | dbo | pets | age | int |
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 (first_name and last_name) the data type is nvarchar, while the data type for the last column (age) is int for integers:
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | DATA_TYPE |
| test_database | dbo | people | first_name | nvarchar |
| test_database | dbo | people | last_name | nvarchar |
| test_database | dbo | people | age | int |
Scenario 3 – Get the data type for a specific column
Lastly, get the data type for the first_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 = 'first_name'
As you may see, the data type for the first_name column is nvarchar:
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | DATA_TYPE |
| test_database | dbo | people | first_name | nvarchar |
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 = 'first_name'
In this case, the CHARACTER_MAXIMUM_LENGTH is 50.