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

4 Comments

Join the conversation! Your thoughts help the community grow.

PANDIYARAJAN N9y ago
Dear sir , i want all columns names except userID,please give SQL query
- saida chary9y ago
  Select name from sys.all_columns where object_id in (select object_id from sys.tables where name = 'News') and name <> 'UserId'
Arvind Pandey11y ago
Dear Sir, how to merge two column in single result..
Arvind Pandey11y ago
very nice