Using STRING_AGG() as a dynamic T-SQL generator

Concatenating strings is an essential practice in the dynamic T-SQL preparation process. A very common task is to generate multiple commands statements concatenated with UNION ALL, i.e. to calculate the number of rows in every table. The traditional way how to do it is that we will declare the @Stmt variable and build the final string with it in the SELECT clause like in this example:

USE [msdb]
GO

DECLARE @Stmt NVARCHAR(MAX) = ''

SELECT @Stmt = @Stmt + 'SELECT ''[dbo].' + [name] + ''', (SELECT COUNT(*) FROM [dbo].[' + [name] + ']) UNION ALL ' + CHAR(13)
FROM [sys].[tables]

PRINT @Stmt
GO

It works pretty well but with one hidden issue to be fixed: there is the last UNION ALL statement we should remove that the query will execute successfully:

It can be achieved with a few string manipulation functions like SET @Stmt = LEFT(@Stmt, LEN(@Stmt) – 11). But this doesn’t look like to be an elegant way. It introduces a constant value in the script (11) and can be pretty tricky in case of more complex dynamic T-SQL with row terminator or other “hidden” characters to be used.

Using STRING_AGG() function we can do the same but much safer and elegant. Let’s try it out:

USE [msdb]
GO

DECLARE @Stmt NVARCHAR(MAX)

SELECT 
	@Stmt = STRING_AGG(
			   'SELECT ''[dbo].' + [name] + ''', (SELECT COUNT(*) FROM [dbo].[' + [name] + '])', 
			   ' UNION ALL ' + CHAR(13)
			  ) 
FROM sys.[tables]

PRINT @Stmt
EXECUTE(@Stmt)
GO

But it looks like there must be something wrong because the statement has crashed with the following error message:

This error was caused by the fact that our final concatenated string is longer than the default 8000 character size limit for strings. We can solve this issue simply by casting our string concatenation expression to NVARCHAR(MAX) data type. This will allow longer than 8000 string to be created as the aggregation result:

USE [msdb]
GO

DECLARE @Stmt NVARCHAR(MAX)

SELECT 
	@Stmt = STRING_AGG(
			   CAST('SELECT ''[dbo].' + [name] + ''', (SELECT COUNT(*) FROM [dbo].[' + [name] + '])' AS nvarchar(MAX)), 
			   ' UNION ALL ' + CHAR(13)
			  )
FROM sys.[tables]

PRINT @Stmt
EXECUTE(@Stmt)
GO

Sorting the concatenation explicitly is the last major improvement we can implement in our generator. It’s simple: just attach the WITHIN GROUP clause and pickup column or multiple columns for sorting:

USE [msdb]
GO

DECLARE @Stmt NVARCHAR(MAX)

SELECT 
	@Stmt = STRING_AGG(
			    CAST('SELECT ''[dbo].' + [name] + ''', (SELECT COUNT(*) FROM [dbo].[' + [name] + '])' AS nvarchar(MAX)), 
			    ' UNION ALL ' + CHAR(13)
			   ) 
			   WITHIN GROUP (ORDER BY [name])
FROM sys.[tables]

PRINT @Stmt
EXECUTE(@Stmt)
GO

Finally, there is still one point to remember: STRING_AGG() is available from SQL Server 2017 first. Prior to this version, I will recommend the solution with the traditional string concatenation or you may also try the FOR XML PATH way because of performance reasons:

USE [msdb]
GO

DECLARE @Stmt NVARCHAR(MAX) = ''

SELECT 
	@Stmt = (
		SELECT TOP(5)  'SELECT ''[dbo].' + [name] + ''', (SELECT COUNT(*) FROM [dbo].[' + [name] + ']) UNION ALL ' + CHAR(10)
		FROM sys.[tables]
		FOR XML PATH('')
		)
PRINT LEFT(@Stmt, LEN(@Stmt) -11)
GO