Different Ways To Get Table Column Names In SQL Server

Introduction

In this simple article, we will discuss different ways to find the particular table’s column names in SQL Server.

Method One

The first approach is using the system table sys.columns. This table results many columns, but we are interested only in the column name, so I am selecting name column from the sys.columns table, which holds the column name

SELECT name AS [Column Name]
FROM sys.columns 
WHERE object_id = OBJECT_ID('[Demo].[OrderDetails]')

The above T-SQL statement results as,

Fig. 1 Column Name using sys.columns

Method Two

The second approach is using the INFORMATION_SCHEMA.COLUMNS. Along with the column name, I have selected the data type of that column as well.

SELECT 
	COLUMN_NAME AS [Column Name], 
	DATA_TYPE AS [Data Type]
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = N'OrderDetails'

The above T-SQL statement results as,

Fig. 2 Column Names using INFORMATION_SCHEMA.COLUMNS

Method Three

The next approach is by using the sys.columns and sys.objects tables.

SELECT  
		SCHEMA_NAME(o.schema_id) [Schema Name], 
		o.Name AS [Table Name], 
		c.Name AS [Column Name]
FROM     sys.columns c 
         JOIN sys.objects o ON o.object_id = c.object_id 
WHERE    o.type = 'U'
ORDER BY o.Name, c.Name

The above T-SQL statement results as,

Fig. 3 using sys.columns and sys.objects table

Method Four

The next approach is using the system stored procedure, sp_help.

sp_help N'Demo.OrderDetails'

The above Stored Procedure results as,

Fig. 4 Using sp_help to get column names of the table

Conclusion

In this article, we have discussed the different ways to get the column names of the a table in SQL Server. We will explore SQL Server in our upcoming articles. Please share your feedback in the comment section.