How to use INFORMATION_SCHEMA.COLUMNS View In SQL

• 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.

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.