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




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



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

select
 TABLE_NAME,
 COLUMN_NAME
from [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.

select
 TABLE_SCHEMA,
 TABLE_NAME,
 string_agg(COLUMN_NAME, ', ') as [Column List]
from [INFORMATION_SCHEMA].[COLUMNS]
group by TABLE_SCHEMA, TABLE_NAME

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

select
 Table_Schema,
 Table_Name,
 String_Agg(Column_Name, ', ') Within Group (Order By Ordinal_Position) as [Column List]
from [INFORMATION_SCHEMA].[COLUMNS]
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

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

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

create table script on SQL Server using String_Agg function







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 - 2018 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems