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.