Skip to content
The Chat Archive
claude-opus-4-20250514-thinking-16k· Anthropic LMArena Human Preference 140K

Build me a dynamic SQL query that will search all columns in a particular table for the…

  1. User

    Build me a dynamic SQL query that will search all columns in a particular table for the condition "WHERE column LIKE 'squirrel'"

    Assume the database is dbo, and that the table is table_one.

  2. Assistant

    Here's a dynamic SQL query that will search all columns in dbo.table_one for the value 'squirrel':

    DECLARE @sql NVARCHAR(MAX) = 'SELECT * FROM dbo.table_one WHERE ';
    DECLARE @conditions NVARCHAR(MAX) = '';
    
    SELECT @conditions = @conditions + 
        CASE 
            WHEN @conditions = '' THEN ''
            ELSE ' OR '
        END + 
        QUOTENAME(COLUMN_NAME) + ' LIKE ''%squirrel%'''
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = 'dbo' 
        AND TABLE_NAME = 'table_one'
        AND DATA_TYPE IN ('varchar', 'nvarchar', 'char', 'nchar', 'text', 'ntext');
    
    SET @sql = @sql + @conditions;
    
    -- Execute the dynamic SQL
    EXEC sp_executesql @sql;
    

    If you want an exact match instead of a partial match, replace LIKE ''%squirrel%'' with = ''squirrel''.

    Here's an alternative version that prints the generated SQL before executing (useful for debugging):

    DECLARE @sql NVARCHAR(MAX) = 'SELECT * FROM dbo.table_one WHERE ';
    DECLARE @conditions NVARCHAR(MAX) = '';
    
    SELECT @conditions = @conditions + 
        CASE 
            WHEN @conditions = '' THEN ''
            ELSE ' OR '
        END + 
        QUOTENAME(COLUMN_NAME) + ' LIKE ''%squirrel%'''
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = 'dbo' 
        AND TABLE_NAME = 'table_one'
        AND DATA_TYPE IN ('varchar', 'nvarchar', 'char', 'nchar', 'text', 'ntext');
    
    SET @sql = @sql + @conditions;
    
    -- Print the generated SQL
    PRINT @sql;
    
    -- Execute the dynamic SQL
    EXEC sp_executesql @sql;
    

    Note that this query:

    • Only searches text-based columns (varchar, nvarchar, char, nchar, text, ntext)
    • Uses %squirrel% for a partial match (contains 'squirrel' anywhere)
    • Combines all conditions with OR, so it returns rows where ANY column contains 'squirrel'
    • Uses QUOTENAME() to properly escape column names that might contain special characters

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.