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