Put SQL output in select query

Question

I have a query which give output as list of columns which we want to put in select query.

we can't add all the columns and the query has some condition will give only the required column as output not all columns.
This column name will be then call in select statement.

select col_name_list from tablename.

Below query is we are using to get the column name list-
DECLARE @tableName nvarchar(max) = 'course'

SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @tableName

EXCEPT

SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS [tc]
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE [ku] ON tc.CONSTRAINT_NAME = ku.CONSTRAINT_NAME
AND ku.table_name = @tableName

Note - we want to make a dynamic sql query as in future it will use 20-30 tables

Answer 1

Try something like this:

declare @tableName varchar(max) = 'course'

declare @sql varchar(max) = concat(
    'select ',
    (
        select string_agg( quotename(COLUMN_NAME), ', ') from (
           SELECT COLUMN_NAME
           FROM INFORMATION_SCHEMA.COLUMNS
           WHERE TABLE_NAME = @tableName
           EXCEPT
           SELECT COLUMN_NAME
           FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS [tc]
           JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE [ku] ON tc.CONSTRAINT_NAME = ku.CONSTRAINT_NAME
           AND ku.table_name = @tableName
       ) d),
    ' from ',
    quotename(@tableName)
)

--print @sql

exec (@sql)