Retrieving Column Names of a Table in SQL Server 2012

In this article, I would like to show how to get the name of all columns of a table in SQL Server. In some cases, you need to get the columns of a specific table. You can write many queries to return all the column names for a table in my database. So let's have a look at a practical example of how to retrieve column names of a table in SQL Server 2012. The example is developed in SQL Server 2012 using the SQL Server Management Studio.

 

Creating table in SQL Server

 

Now we create a table named News. The following is the sample data for the News Table:

 

 

The following queries will give the table's column names in SQL Server.

 

1. sp_help procedure

 

The sp_help procedure is used to get information about a current database object. The database object may be a table, view or stored procedure. To get a column name of a table we use sp_help with the name of the object or table name. sp_help will return all the column names of the object. The following query will return the table's column names.

sp_help News

In the preceding query we use an object named News table.

 

Output

 

 

2. Simple Select Query

 

It is a simple way to get the column names of a table using the top clause. You can use the following select statement to retrieve a table's columns from the news object or table:

SELECT TOP 5 * FROM News

The preceding query returns the top 5 records from the news table. If we use top with 0 then it will return only the column names of the table. The following query will give the table's column names:

SELECT TOP 0 * FROM News

Output

 

 

3. sp_columns procedure

 

sp_columns is used to get the information for the specified objects that can be queried in the current environment. To get the column name of a table we use sp_help with the name of the object or table name. sp_columns returns all the column names of the object. The following query will return the table's column names:

sp_columns @table_name = 'News'

Output

 

 

You can also use the following query to get the table's column names:

SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('News')

Output

 

 

4.  Information_ Schema

 

Information schema views provide an internal view of the SQL Server metadata independent of the system table. You can use the select statement with the Information Schema to retrieve a table's columns from the news object or table. The following query will give the table's column names:

SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'News'

Output