2
\$\begingroup\$

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
\$\endgroup\$
1
\$\begingroup\$

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.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.