gpt-4.1-2025-04-14· OpenAI
LMArena Human Preference 140K
Build me a dynamic SQL query that will search all columns in a particular table for the…
- 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.
- Assistant
Certainly! To search all columns in
dbo.table_onefor the value'squirrel'using a dynamic SQL query, you can use the following approach in SQL Server (T-SQL):Step 1: Build the Dynamic SQL
This script will generate and execute a query that checks every column in
table_onefor the value'squirrel'usingLIKE.DECLARE @TableName NVARCHAR(128) = 'table_one'; DECLARE @SchemaName NVARCHAR(128) = 'dbo'; DECLARE @SearchValue NVARCHAR(100) = 'squirrel'; DECLARE @SQL NVARCHAR(MAX) = ''; SELECT @SQL = STRING_AGG( QUOTENAME(c.name) + ' LIKE ''%' + @SearchValue + '%''', ' OR ') FROM sys.columns c JOIN sys.tables t ON c.object_id = t.object_id JOIN sys.schemas s ON t.schema_id = s.schema_id WHERE t.name = @TableName AND s.name = @SchemaName AND c.system_type_id IN (167, 175, 231, 239); -- char, varchar, nchar, nvarchar SET @SQL = 'SELECT * FROM ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + ' WHERE ' + @SQL; -- Print or execute the dynamic SQL PRINT @SQL; -- EXEC sp_executesql @SQL;Notes:
- This script only checks character columns (
char,varchar,nchar,nvarchar). If you want to include other types, adjust thesystem_type_idfilter. - The
STRING_AGGfunction is available in SQL Server 2017+. For earlier versions, useFOR XML PATHto concatenate. - Uncomment the
EXEC sp_executesql @SQL;line to actually run the query.
If you need this for another RDBMS (like MySQL, PostgreSQL, etc.), let me know!
- This script only checks character columns (
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.