Comma-Separating Table Columns into Lists in SQL Server

Hello everyone,

In this article, I will try to give information about separating table columns with commas and making them a list in SQL Server.

In SQL Server, in some cases, the number of columns in your table may be high. Instead of typing the column names one by one after SELECT, you can list all the column names by using the code below. Or you can dynamically use this query in various places.

You can easily do this using the code below.

DECLARE @TABLE_NAME VARCHAR(128);
DECLARE @SCHEMA_NAME VARCHAR(128);

SET @TABLE_NAME = 'Categories';
SET @SCHEMA_NAME = 'dbo';

DECLARE @vvc_ColumnName VARCHAR(128);
DECLARE @vvc_ColumnList VARCHAR(MAX);

IF @SCHEMA_NAME = ''
BEGIN
    PRINT 'Error : Schema not defined!';
    RETURN;
END;

IF NOT EXISTS
(
    SELECT *
    FROM sys.tables T
        JOIN sys.schemas S
            ON T.schema_id = S.schema_id
    WHERE T.name = @TABLE_NAME
          AND S.name = @SCHEMA_NAME
)
BEGIN
    PRINT 'Error : Table Name : ''' + @TABLE_NAME + ''' and Schema Name : ''' + @SCHEMA_NAME + ''' not found in database';
    RETURN;
END;

DECLARE TableCursor CURSOR FAST_FORWARD FOR
SELECT CASE
           WHEN PATINDEX('% %', C.name) > 0 THEN
               '[' + C.name + ']'
           ELSE
               C.name
       END
FROM sys.columns C
    JOIN sys.tables T
        ON C.object_id = T.object_id
    JOIN sys.schemas S
        ON S.schema_id = T.schema_id
WHERE T.name = @TABLE_NAME
      AND S.name = @SCHEMA_NAME
ORDER BY column_id;


SET @vvc_ColumnList = '';

OPEN TableCursor;
FETCH NEXT FROM TableCursor
INTO @vvc_ColumnName;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @vvc_ColumnList = @vvc_ColumnList + @vvc_ColumnName;

    -- get the details of the next column
    FETCH NEXT FROM TableCursor
    INTO @vvc_ColumnName;

    -- add a comma if we are not at the end of the row
    IF @@FETCH_STATUS = 0
        SET @vvc_ColumnList = @vvc_ColumnList + ',';
END;

CLOSE TableCursor;
DEALLOCATE TableCursor;

PRINT 'Comma Separated Column Names';
PRINT '--------------------------------------------------';
PRINT @vvc_ColumnList;

When you run the above code, you will see a result similar to the one below.

As you can see, we have separated the table columns with commas and turned them into a list.

Good luck to everyone in business and life.