T-SQL: Get a tab-separated list of column names in a table - What's wrong with my example?-CodePudding

This SQL returns a comma-separated list of column names for the table 'MyTable'

DECLARE @colnames VARCHAR(max);

SELECT
    @colnames = COALESCE(@colnames   ',', '')   column_name
FROM
    CORP_MLR_Rebates.INFORMATION_SCHEMA.COLUMNS
WHERE
    table_name = 'MyTable';

SELECT @colnames;

Why doesn't the following give me a tab-separated list the same columns? Instead, it is space-separated.

DECLARE @colnames VARCHAR(max);

SELECT
    @colnames = COALESCE(@colnames   char(9), '')   column_name
FROM
    CORP_MLR_Rebates.INFORMATION_SCHEMA.COLUMNS
WHERE
    table_name = 'MyTable';

SELECT @colnames;

CodePudding user response：

In my testing it does work, I guess it just depends on how you retrieve the results (I have to output the result to a file to get the correct results)

CodePudding user response：

You will likely get spaces using the SSMS GUI. Returning the results as text or to a file will give you tabs; e.g. CHAR(9). Consider these three queries that all do the job:

;--== 1. Updatable Variable
DECLARE @colnames VARCHAR(max);

SELECT @colnames = COALESCE(@colnames   CHAR(9), '')   column_name
FROM   INFORMATION_SCHEMA.COLUMNS AS c;

SELECT ColNames = @colnames;

;--== 2. Using STRING_AGG
SELECT ColNames = STRING_AGG(c.COLUMN_NAME , CHAR(9))
FROM   INFORMATION_SCHEMA.COLUMNS AS c;

;--== 3. Using FOR XML PATH
SELECT ColNames = 
  (SELECT c.COLUMN_NAME CHAR(9) FROM INFORMATION_SCHEMA.COLUMNS AS c FOR XML PATH(''));

Results:

If we switch to Results as Text:

Now we get (note the tabs):