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.