How to Concatenate SQL Server Columns for each Table Query Dynamically in SQL Server ?

Basically To concatenate columns for each table dynamically in SQL Server, you can use a similar approach as the one for retrieving record counts. You’ll need to query the INFORMATION_SCHEMA.COLUMNS view to get the column names for each table and then dynamically generate a SQL query to concatenate those columns. Here's a step-by-step explanation:

1.Querying INFORMATION_SCHEMA.COLUMNS to Get Column Names:

Start by querying the INFORMATION_SCHEMA.COLUMNS view to get a list of all columns for each table. This query retrieves the schema name, table name, and column name for each column.

SELECT 
    TABLE_SCHEMA AS schema_name,
    TABLE_NAME AS table_name,
    COLUMN_NAME AS column_name
FROM 
    INFORMATION_SCHEMA.COLUMNS;

2.Dynamically Generating SQL Queries:

Use the result from the previous query to dynamically generate SQL queries for each table. You can again use a cursor, a loop, or dynamic SQL query for this. Here’s an example using a cursor:

DECLARE @schema_name NVARCHAR(128);
DECLARE @table_name NVARCHAR(128);
DECLARE @column_name NVARCHAR(128);
DECLARE @sql_query NVARCHAR(MAX);

DECLARE column_cursor CURSOR FOR
SELECT 
    TABLE_SCHEMA AS schema_name,
    TABLE_NAME AS table_name,
    COLUMN_NAME AS column_name
FROM 
    INFORMATION_SCHEMA.COLUMNS;

OPEN column_cursor;

FETCH NEXT FROM column_cursor INTO @schema_name, @table_name, @column_name;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql_query = 'SELECT ''' + @schema_name + '.' + @table_name + ''' AS table_name, CONCAT(' + STRING_AGG(@column_name, ', '' - '', ') WITHIN GROUP (ORDER BY @column_name) + ') AS concatenated_columns FROM ' + @schema_name + '.' + @table_name;
    EXEC sp_executesql @sql_query;

    FETCH NEXT FROM column_cursor INTO @schema_name, @table_name, @column_name;
END

CLOSE column_cursor;
DEALLOCATE column_cursor;

This script generates a dynamic SQL query for each table, concatenates the columns, and prints the result.

3.Viewing Results:

When you execute the script, you’ll see a result set with columns table_name and concatenated_columns for each table in the database.

Note:

Be cautious when using dynamic SQL queries, and make sure to validate and sanitize inputs to prevent SQL injection. Additionally, the STRING_AGG function used here is available in SQL Server 2017 and later versions. If you are using an earlier version, you may need to use alternative methods for string concatenation.