Tutorial Gateway

Get Column Names From Table in SQL

by

In this article, we will show you, How to write a SQL Query to Get Column Names From Table in SQL Server with an example. This is one of the common SQL Interview Question that you might face in the interviews. For this example, We are going to use the below-shown data

Get Column Names From Table in SQL Server 0

Above screenshot will show you the data inside the NewCustomer table present in the SQL Tutorial database.

Get Column Names From Table in SQL Server Example 1

In this example, we will show you, How to Get Column names in SQL Server using INFORMATION_SCHEMA.

-- Query to Get Column Names From Table in SQL Server
USE [SQL Tutorial]
GO
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'NewCustomers'

OUTPUT

Get Column Names From Table in SQL Server 1

You can use the below query to get all the information about the Table

-- Query to Get Column Names From Table in SQL Server
USE [SQL Tutorial]
GO
SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'NewCustomers'

OUTPUT

Get Column Names From Table in SQL Server 2

Get Column Names From Table in SQL Server Example 2

In this example, we will show you, How to Get Column names in SQL Server using sys.columns.

T-SQL CODE

-- Query to Get Column Names From Table in SQL Server
USE [SQL Tutorial]
GO
SELECT name
FROM sys.columns 
WHERE OBJECT_ID = OBJECT_ID('NewCustomers')

OUTPUT

Get Column Names From Table in SQL Server 3

Let me show you what will happen if we replace the name column with *

-- Query to Get Column Names in SQL Server
USE [SQL Tutorial]
GO
SELECT *
FROM sys.columns 
WHERE OBJECT_ID = OBJECT_ID('NewCustomers')

OUTPUT

Get Column Names From Table in SQL Server 4