SQL Server数据发现与分类查询：将STRING_AGG替换为STUFF以兼容2016及更早版本

Got it, let's tackle this compatibility issue head-on! STRING_AGG is super convenient but only exists in SQL Server 2017 and later. The classic replacement for row concatenation in older versions is the STUFF + FOR XML PATH pattern, and I'll show you exactly how to swap it into your code.

The Modified Dynamic Query Section #

Here's the updated part of your code that replaces the STRING_AGG logic—this is the only section you need to change:

SET @DynamicQuery = N'
SELECT @Sample = STUFF(
    (SELECT '', '' + CAST(x.' + @CurrentColumnName + ' AS VARCHAR(MAX))
     FROM ' + @CurrentTableName + ' AS x
     WHERE x.' + @CurrentColumnName + ' IS NOT NULL
     TOP 5
     FOR XML PATH(''''), TYPE).value(''.'', ''VARCHAR(MAX)''),
    1, 2, '''')
'

Breakdown of the Fix #

• FOR XML PATH(''): Converts the top 5 non-null values into a single string with leading commas (e.g., , 980, 365, 771).
• .value(''.'', ''VARCHAR(MAX)''): Extracts raw text from the XML output to avoid XML-encoded characters (like & for &).
• STUFF(..., 1, 2, ''''): Removes the leading , by deleting the first two characters of the concatenated string.

Full Updated Query #

Here's your complete, compatibility-ready code with the fix applied:

DECLARE @TableName VARCHAR(100) = 'Product'
DROP TABLE IF EXISTS #ColumnsToDisplay
SELECT
 ROW_NUMBER () OVER (ORDER BY tab.name) AS Iteration,
 SCHEMA_NAME (tab.schema_id) AS schema_name,
 tab.name AS table_name,
 col.name AS column_name,
 CAST(NULL AS VARCHAR(MAX)) AS DataSample
INTO #ColumnsToDisplay
FROM sys.tables AS tab
JOIN sys.columns AS col ON col.object_id = tab.object_id
WHERE tab.name = @TableName

DECLARE @Iterations INT = 0, @CurrentIteration INT = 1;
SELECT @Iterations = MAX (Iteration) FROM #ColumnsToDisplay

WHILE @CurrentIteration <= @Iterations
BEGIN
 DECLARE @CurrentTableName VARCHAR(100) = '', @CurrentColumnName VARCHAR(100) = '', @DynamicQuery NVARCHAR(1000) = N''
 DECLARE @Sample VARCHAR(MAX)
 SET @CurrentTableName = '';
 SET @DynamicQuery = N'';

 SELECT @CurrentTableName = CONCAT (ttq.schema_name, '.', ttq.table_name),
 @CurrentColumnName = ttq.column_name
 FROM #ColumnsToDisplay AS ttq
 WHERE ttq.Iteration = @CurrentIteration

 IF (@CurrentTableName = '')
 BEGIN
 SET @CurrentIteration += 1
 CONTINUE
 END

 -- Updated dynamic query for SQL Server 2016 and earlier
 SET @DynamicQuery = N'
 SELECT @Sample = STUFF(
     (SELECT '', '' + CAST(x.' + @CurrentColumnName + ' AS VARCHAR(MAX))
      FROM ' + @CurrentTableName + ' AS x
      WHERE x.' + @CurrentColumnName + ' IS NOT NULL
      TOP 5
      FOR XML PATH(''''), TYPE).value(''.'', ''VARCHAR(MAX)''),
     1, 2, '''')
 '

 EXECUTE sys.sp_executesql @DynamicQuery, N'@Sample VARCHAR(MAX) OUTPUT', @Sample = @Sample OUTPUT

 UPDATE #ColumnsToDisplay SET DataSample = @Sample WHERE Iteration = @CurrentIteration
 SET @CurrentIteration += 1
END

SELECT
 ctd.Iteration,
 ctd.schema_name,
 ctd.table_name,
 ctd.column_name,
 ctd.DataSample
FROM #ColumnsToDisplay AS ctd

Quick Verification #

This modified code will produce exactly the same comma-separated sample output as your original query, but now it works seamlessly on SQL Server 2016 and older versions. The TOP 5 filter and non-null check remain intact, so you'll still get a clean preview of your column data.

内容的提问来源于stack exchange，提问作者Francesco Mantovani