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
Development resources, articles, tutorials, code samples, tools and downloads for ASP.Net, SQL Server, Reporting Services, T-SQL, Windows, AWS, SAP HANA and ABAP


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

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

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

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
Code

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.



SQL Server

SQL Server 2019 Installation
download SQL Server 2019
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012
MacOS ve SQL Server 2019


Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.