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.
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:
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:
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