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.

1 2 3 4 5 6 7 8

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.

1	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:

1 2 3 4 5 6

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.

1	EXEC SP_HELP 'Sales.Orders'