Getting The List Of Column Names Of A Table In SQL Server

There are several ways to get the the list of column names of a table on a specific SQL Server database. In this article, I will go through these methods.

1. Information Schema View Method

You can use the information schema view INFORMATION_SCHEMA.COLUMNS. In an earlier article, I have used this schema view to check if column exists. Here is an example of how to use it and get the names of all the columns in a specific table.

SELECT 
        COLUMN_NAME, ORDINAL_POSITION, DATA_TYPE
    FROM 
        INFORMATION_SCHEMA.COLUMNS
    WHERE 
        TABLE_NAME = 'Orders' 
    ORDER BY 2
GO

2. System Stored Procedure SP_COLUMNS Method

Another method is to use the system stored procedure SP_COLUMNS. Here is an example of how to use it and get the names of all the columns in a specific table.

EXEC SP_COLUMNS 'Orders'

3. SYS.COLUMNS Method

SYS.COLUMNS is a system catalogue view which gives the details about the columns from all the tables in the database. You can use a WHERE condition to limit the list of columns to a specific table. Here is an example:

SELECT 
		NAME, COLUMN_ID
	FROM 
		SYS.COLUMNS 
	WHERE 
		object_id = OBJECT_ID('Sales.Orders')

4. SP_HELP Method

SP_HELP system procedure is another way to get the list of all column names from a specific table. Along with table name and column names, SP_HELP will also other details like identity columns, index names and constraint types. Here is an example.

EXEC SP_HELP 'Sales.Orders'

Reference

• About information schema view INFORMATION_SCHEMA.COLUMNS at Microsoft Docs.
• About system catalogue view SYS.COLUMNS at Microsoft Docs.
• About system stored procedure SP_HELP at Microsoft Docs.