Get Table Column Names List in SQL Server by Code
This SQL tutorial will show SQL Server database developers how to get columns names of a database table in SQL Server using various methods. The list of table columns can be obtained programmatically by executing queryies on system catalog views like sys.columns or information schema views, etc.
Let's summarize the methods for SQL developers can use to read database table column names in SQL Server:
sys.columns or syscolumns system catalog view,
information_schema.columns system view,
sp_columns system stored procedure
Query sys.columns to Get Column Names of a Database Table
Sys.Columns is a built-in SQL Server Object Catalog View which returns data for each column of an database object like database tables, views, table-valued functions, etc. By executing a SQL query on sys.columns or syscolumns view, T-SQL programmer can get the list of column names of a table beside many other column data like data type, length, collation, etc.
SELECT Name FROM sys.columns
WHERE object_id = object_id('Production.Product')
-- OR very similarly,
SELECT Name FROM syscolumns WHERE id = object_id('Production.Product')
Please refer to MSDN for more detailed information about sys.columns system view.
information_schema.columns
SQL developers can query SQL Server System Information Schema View information_schema.columns for getting detailed data about a column of a database table.
Of course if the SQL programmer filters information_schema.columns view for a specific table or view, he or she will be able to list all columns of that table.
SQL developers and database administrators can refer to MSDN for official documentation about information_schema.columns system view and for other information schema views.
SELECT column_name FROM information_schema.columns
WHERE table_schema ='HumanResources' AND table_name = 'Employee'
Execute sp_columns Procedure for Table Columns
sp_columns is a SQL Server system stored procedure which returns data about column information in detail of catalog objects like database tables, views, etc.
If sp_columns stored procedure is called with table name parameter, the list of columns of the database table will be obtained.
While executing sp_columns procedure, developers can also provide the @table_qualifier parameter for setting the schema name if required.
exec sp_columns 'Address'
exec sp_columns @table_name = 'Address'
For more information on syntax and data returned from sp_columns, please refer to pointed online MSDN documentation.
List Table Columns as Comma Seperated for Dynamic SQL Query
Programmers and administrators can define sql variables for column list of a table and use this in their dynamic SQL queries. For such cases where developers need to build comma seperated list of table column names, they can modify following SQL script.
declare @ColumnList nvarchar(max)
SELECT @ColumnList =
STUFF(
(
SELECT
',' + name -- QUOTENAME(name)
FROM sys.columns
WHERE object_id = object_id('Production.Product')
FOR XML PATH(''),TYPE
).value('.','VARCHAR(MAX)'
), 1, 1, ''
)
select @ColumnList
If you plan to use columns names in an other SQL script, you can think of using the QUOTENAME() SQL function for field names with spaces or special characters in them.