SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software Kodyaz SQL Server and T-SQL Development Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.




download SQL Server 2016
download SQL Server 2014
download SQL Server 2012



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')

list table column names using sys.columns

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'

SQL Server Information Schema View for columns of a table


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'

list of table columns using sp_columns SQL Server system procedure

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.







Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

SQL Server 2008 Blog

Certification Exams Blog

Reporting Services Blog

Analysis Services Blog

MS SQL Server Forums







Copyright © 2004 - 2017 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems