Query column names and then using them in statements

Question

I currently have a table with over 250 columns, in which ~10-15 columns have column names that have a specific prefix, namely EQP_.

Assume that you do not want to type the names of these columns, but still want only a result set containing those columns.

Some possible solutions I thought of:

• Using a common_table_expression
• Using a table variable with a function
• Using an INNER JOIN with INFORMATION_SCHEMA

All of these seem like overkill to me, so I ended using a temp table and dynamic SQL to get something working. I am not a fan of dynamic SQL, so I was hoping to get some feedback on how to do this better.

Query

SELECT TOP 15 COLUMN_NAME 'name', 0 'selected'
INTO #columns
FROM Production.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'it_master'
AND COLUMN_NAME LIKE 'EQP_%' 

DECLARE @sql NVARCHAR(MAX)
SET @sql = 'SELECT TOP 10 ' ;

WHILE EXISTS(SELECT 1 FROM #columns WHERE [selected] = 0 )
BEGIN

    DECLARE @column NVARCHAR(100)

    SELECT TOP 1 @column  = [name] FROM #columns WHERE [selected]= 0

    SET @sql = @sql + @column + ', '

    UPDATE c
    SET [selected] = 1
    FROM #columns c
    WHERE [name] = @column

END

SET @sql = LEFT(@sql, LEN(@sql) - 1) + ' FROM it_master '

EXECUTE sp_executesql @sql

IF OBJECT_ID('tempdb..#columns') IS NOT NULL
    DROP TABLE #columns

Answer 1

There is no need for loop/temporary table at all. You could use GROUP_CONCAT equivalent in SQL Server(STUFF + XML) trick:

DECLARE @col NVARCHAR(MAX) = STUFF((SELECT  ',' + QUOTENAME(COLUMN_NAME)
                                    FROM INFORMATION_SCHEMA.COLUMNS
                                    WHERE TABLE_NAME = N'it_master'
                                      AND TABLE_SCHEMA = 'dbo'
                                      AND COLUMN_NAME LIKE 'EQP_%'
                                    ORDER BY COLUMN_NAME
                                    FOR XML PATH(''))
                                   ,1,1,'');

DECLARE @query NVARCHAR(MAX) = N'SELECT <placeholder> FROM it_master;';
SET @query = REPLACE(@query,'<placeholder>', @col);

EXEC [dbo].[sp_executesql] @query;

LiveDemo

Notes:

• It is a good practice to filter from INFORMATION_SCHEMA.COLUMNS with table name and schema_name (tables with the same name could exist in many schemas)
• Concatenating main query with + is not readable. You could use REPLACE or printf equivalent

SQL Server 2012+:

DECLARE @query NVARCHAR(MAX) = FORMATMESSAGE('SELECT %s FROM it_master;', @col);

LiveDemo2

---

Another way is to use SQL Server Management Studio:

Object Explorer -> Database_Name -> Tables -> Table_Name -> Columns -> (Click Filter and type EQP optional)

Highlight all needed columns with SHIFT and drag and drop to query pane.