Put SQL output in select query

Sarvesh Pandey 71 Reputation points
2020-11-27T10:59:03.107+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,655 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,551 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Viorel 111.8K Reputation points
    2020-11-27T11:11:02.653+00:00

    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)
    
    1 person found this answer helpful.