SQL: Create a list of columns for a table using T-SQL and STRING_AGG

I often need to create a list of columns for a table. Sometimes it’s because I’m generating scripts but there are other times that I need it for dynamic code.

Let’s use WideWorldImporters as a sample. Imagine I need to get a list of columns for the Sales.Orders table.

The wrong way

I’ve seen people doing this the wrong way. They try to use a SELECT statement that joins a variable to itself like this:

DECLARE @ColumnList nvarchar(max) = N'';

SELECT @ColumnList = @ColumnList + c.[name] + N',' 
FROM sys.columns AS c
INNER JOIN sys.tables AS t
ON t.object_id = c.object_id 
INNER JOIN sys.schemas AS s
ON s.schema_id = t.schema_id 
WHERE s.[name] = N'Sales'
AND t.[name] = N'Orders'
AND c.is_computed = 0
ORDER BY c.column_id;

SET @ColumnList = LEFT(@ColumnList, LEN(@ColumnList) - 1);

SELECT @ColumnList;

There are several things wrong with this.

First, if you’re scripting, you should always use QUOTENAME to put quotes around object names when you’re generating them in script. I don’t love having to quote them all. I do wish there was a QUOTE_NAME_IF_NEEDED function instead. It could automatically quote names with spaces, invalid characters, reserved words, etc. I’ve asked Microsoft for this a number of times. It would greatly improve scripting.

But the bigger issue is that concatenating a string like this with a SELECT has several ways it can go wrong. It’s a good example of code that seems to work when you tested it, but might break later.

The right way

As long as you’re on the SQL Server 2017 or later versions of T-SQL, the STRING_AGG function is perfect for this.

DECLARE @ColumnList nvarchar(max) = 
    (SELECT STRING_AGG(QUOTENAME(c.[name]), N', ')
            WITHIN GROUP (ORDER BY c.column_id)                                  
     FROM sys.columns AS c
     INNER JOIN sys.tables AS t
     ON t.object_id = c.object_id 
     INNER JOIN sys.schemas AS s
     ON s.schema_id = t.schema_id 
     WHERE s.[name] = N'Sales'
     AND t.[name] = N'Orders'
     AND c.is_computed = 0);

SELECT @ColumnList;

It will work reliably and do what’s required. Note that aggregates like this aren’t normally ordered, and so to get the required output order, I’ve used the WITHIN GROUP clause. You could just change c.column_id to c.[name] if you want to order the columns alphabetically.

Now, if you’re on an earlier version of SQL Server, for reliable code, I’d be selecting the column list into a table variable, then using a loop to aggregate it from there. That would also be reliable, albeit a bit slower.

2021-01-05