I currently have a table with over 250 columns, in which ~10-15 columns have column names that have a specific prefix, namely
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
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.
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