How to use sys.columns View In SQL

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

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.