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 Concatenated Column List of Database Tables on SQL Server

Transact-SQL developers working on SQL Server 2017 can use String_Agg function for aggregating string table column values even more grouped by using "Within Group" option. In this T-SQL tutorial, I want to show database programmers how they can use SQL function String_Agg() to create a list of database tables and the column list of that table in a concatenated string form.

Assume that using SQL, database developer needs to list user tables and their columns existing in a SQL Server database.
One of the datasources that SQL developers can use is information schema view INFORMATION_SCHEMA.COLUMNS


Here is only a small part of the above SQL query output when I execute SQL Select on WideWorldImporters database

get list of table column names from system view on SQL Server

In this tutorial, we want to list all column names aggregated and concatenated into a string values and displayed next to table name without repeating the table.
So database developers should change above SQL query as follows.

 string_agg(COLUMN_NAME, ', ') as [Column List]

When you execute it again on SQL Server sample database, it will produce the following result

table column list concatenated using SQL String_Agg() function

Another enhancement to above string aggregation function String_Agg() is sorting the columns within the concatenated list.
For example, above SQL query listed the columns in an alphabetical order in the concatenated list.
If SQL Server developer want to sort columns in the ordinal position in table definition then SQL query should be modified as follows with using new clause "Within Group (Order By sort_column)"

Here is the sample SQL query

 String_Agg(Column_Name, ', ') Within Group (Order By Ordinal_Position) as [Column List]
group by Table_Schema, Table_Name

And the output result set is as follows; you see in this case, in general IDENTITY columns are listed as the first column of the concatenated column list

SQL Server string_agg function within group by clause for string concatenation

Within Group (Order By ...) for String_Agg() SQL function provides a powerful tool for developers dealing with string aggregation.

If database developer requires to list additional tables like system tables, then instead of Information_Schema.Column system view, sys.columns system view can be queried as demonstrated in the next step

 object_name(object_id) as [Table Name],
 string_agg(name,', ') Within Group (Order By column_id) as [Column List]
from sys.columns
group by object_id

Create Table Script on SQL Server Database using String Concatenation

Of course, if as a database programmer you put aside the constraints, identity columns, index definitions, etc following SQL statement could be used for scripting Create Table command on SQL Server for all database tables or for a specific SQL table.

 'Create Table ' + Table_Schema + '.' + Concat(Table_Name, '_backup') + ' (
 ' +
 Column_Name + ' ' +
 Data_Type +
  when null then ''
  when -1 then case when Data_Type = 'geography' or Data_Type = 'xml' then '' else '(max)' end
   case when DATA_TYPE = 'decimal' then
    '(' + convert(varchar(10), NUMERIC_PRECISION) + ',' + convert(varchar(10), Numeric_Scale) + ')'
   ISNULL('(' + convert(varchar(10), CHARACTER_MAXIMUM_LENGTH) + ')','')
  end +
 CASE when IS_NULLABLE = 'YES' then ' NULL ' else ' NOT NULL ' end
 , ', ') Within Group (Order By Ordinal_Position) + '
 ' + ')' as [Table Create Script]
group by Table_Schema, Table_Name

create table script on SQL Server using String_Agg function

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.