How to Get the Data Type of Columns in SQL Server?

SQL Server is a widely used Relational Database Management System (RDBMS) that allows users to create and manage databases effectively. SQL Server offers the SQL Server Management Studio which defines the database development and administration. In this article, we will learn how to retrieve the data type of columns in tables stored in our SQL Server databases.

How to Get the Data Type of Columns in SQL Server?

When working with SQL Server databases it is important to understand the data types of our columns for efficient data management and application development. There are 2 methods through which we can get the data type of columns in SQL Server are explained below

1. Using INFORMATION_SCHEMA.COLUMNS View
   
2. Using sys.columns View

Let's set up an environment

To understand how we can get the data type of columns in SQL Server, we will consider the table Customer as shown below:

1. Using INFORMATION_SCHEMA.COLUMNS View

• To get the data type of columns in SQL Server we can use the DATA_TYPE column from the INFORMATION_SCHEMA.COLUMNS view.
  
• This is a standard view provided by the SQL Server Management Studio which belongs to the INFORMATION_SCHEMA database which is a special kind of database that stores the metadata of the databases, tables, columns, and other objects stored in the system.
  
• Following is the syntax to use INFORMATION_SCHEMA.COLUMNS to get the data type of the columns in SQL Server.

Syntax:

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

Explanation: The syntax to get the data type of the columns is simple. We will select the COLUMN_NAME, DATA_TYPE field from the INFORMATION_SCHEMA.COLUMNS and then we will have to mention the name of the table whose data type of the columns is required.

Example: To get the data type of the columns of the Table Customer we will have to run the following query

Query:

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

Output:

Explanation: The following query returns an output table having two columns, where the first column denotes the name of the columns of the table customer and the second columns denotes the data type of the corresponding column.

2. Using sys.columns View

• In SQL server the sys.columns is a system catalog view that contains a row for each column of user defined tables.
  
• It provides metadata about each column in the table. We can query the sys.columns view to get the data type of each column present in our table.
  
• Following is the syntax to get the data type of columns using sys.columns in SQL server

Syntax:

SELECT COLUMN_NAME, TYPE_NAME(user_type_id) AS DATA_TYPE 
FROM sys.columns 
WHERE object_id = OBJECT_ID('YourTableName');

Explanation: To get the data type of the column provide the name of the table after the object id in the query and mention the name of the columns of your table in place of column_name in the query.

Example: To get the data type of the columns of the Table Customer we will have to run the following query

Query:

SELECT name, TYPE_NAME(user_type_id) AS DATA_TYPE
FROM sys.columns
WHERE object_id = OBJECT_ID('Customers');

Output:

Explanation: The following query returns an output table having two columns where the first column name denotes the name of the columns of the table customer and the second columns denotes the data type of the corresponding column from the table.

Conclusion

In conclusion, SQL Server provides efficient tools for database management, including the ability to retrieve column data types. Using either the INFORMATION_SCHEMA.COLUMNS view or the sys.columns catalog view, users can easily obtain the data type of columns in their SQL Server databases and helping effective database administration and development.