How To Find Column Name In Table SQL Server

It is easy to retrieve all the column names in a table. Let’s understand the whole process using a real-time example. I have an existing table named Vehicle. I want to know the column names available inside that table. There are two simple approaches to getting this.

Approach-1 Using the sys.columns

We can execute the SQL query below to get column names from a table in the SQL Server.

Syntax

SELECT name FROM sys.columns WHERE object_id = OBJECT_ID('Your table name')

Example

You can execute the query below to retrieve the Vehicle table’s column details.

SELECT name FROM sys.columns WHERE object_id = OBJECT_ID('Vehicle')

After you execute the above query, I got the lists of column names from the table Vehicle.

Check out How To Find Table With Column Name in SQL Server

Approach-2 Using INFORMATION_SCHEMA.COLUMNS

We can execute the below query to get the details of the column names

Syntax

SELECT Column_Name 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'Your table name'

Example

You can execute the query below to retrieve the Product table’s column details.

SELECT Column_Name 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'Product'

After executing the above query, I got the expected output below.

Check out How to check if column exists in SQL Server database

Approach-3 Using the sp_columns stored procedure

You can also execute the sp_columns stored procedure for this purpose.

Syntax

EXEC sp_columns 'Your table name'

Example

You can execute the query below to retrieve the Orders table’s column details.

EXEC sp_columns 'Orders'

After executing the above query, I got the expected output, as shown in the screenshot below.

Check out How To Add A Column To A Table In SQL Server

Approach-4 Using the SQL Server Management Studio

Expand the table node and then expand the columns node. As shown in the screenshot below, you will see the lists of column names belonging to the table Vehicle.

Conclusion

Database administrators and developers need to find column names in SQL Server tables. The methods discussed flexible ways to retrieve column information. You can use sys.columns, INFORMATION_SCHEMA.COLUMNS, sp_columns, as discussed in the article above.

You may also like following the articles below.

• How to add a column to a table in SQL Server
• How to change column name in SQL Server