How To Check Datatype Of Column In SQL Server

Checking the Datatype Of a Column in an SQL Server is a basic task for a SQL developer or an SQL DBA. In this article, let me guide you through all the approaches to do this.

How To Check Datatype Of Column In SQL Server

Let us discuss all the approaches individually.

Approach-1 Using SQL Server Management Studio (SSMS)

To check datatype of column in SQL Server, follow the below steps.

1. Open SQL Server Management Studio and connect to your SQL server instance.

2. Expand the databases folder, then expand the specific database folder, and then expand the tables folder. Now right-click on the table name and click on the Design option, as shown in the below screenshot.

3. Now, you can see the Data type for each column, as shown in the screenshot below.

Approach-2: Using INFORMATION_SCHEMA.COLUMNS

We can execute the query below to get each column’s data types for a specified table.

SELECT COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'AzureProduct'

After executing the above query, I got the expected output, as shown in the screenshot below.

Or, We can also specify the column name and the table name using the below query.

SELECT COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'AzureProduct' AND COLUMN_NAME = 'ProductName'

After executing the above query, I got the expected output as shown in the screenshot below.

Approach-3 Using sys.dm_exec_describe_first_result_set

We can also query sys.dm_exec_describe_first_result_set for this purpose. We can execute the below query for this purpose.

SELECT name, system_type_name
FROM sys.dm_exec_describe_first_result_set(N'SELECT * FROM AzureProduct', NULL, 0)

After executing the above query, I got the output as expected, as shown in the screenshot below.

Approach-4 Using sys.columns

We can also query the sys.columns for this purpose.

SELECT n.name AS ColumnName, d.name AS DataType
FROM sys.columns n
INNER JOIN sys.types d ON n.user_type_id = d.user_type_id
WHERE OBJECT_ID = OBJECT_ID('AzureProduct')

After executing the above query, I got the expected output, as shown in the screenshot below.

Approach-5 Using sp_help stored procedure

The Sp_help stored procedure can also help you with this purpose. We can also execute the query below.

EXEC sp_help 'AzureProduct'

After executing the above query, I got the expected output as shown below.

Conclusion

Checking datatype of column in SQL Server is so easy using the above 5 simple approaches as mentioned in this article. Now, it is your turn to choose the best approach that suits your requirements.

You may also like following the articles below.

• How to Add Column to a Table in SQL Server?
• An Explicit Value For The Identity Column In Table
• How To Get The Connection String From SQL Server
• How To Get Column Description In SQL Server