Tutorial :SQL query to return all rows where one or more of the fields contains a certain value


Is it possible to run a select on a table to quickly find out if any (one or more) of the fields contain a certain value?

Or would you have to write out all of the column names in the where clause?


Dig this... It will search on all the tables in the db, but you can mod it down to just one table.

/*This script will find any text value in the database*/  /*Output will be directed to the Messages window. Don't forget to look there!!!*/    SET NOCOUNT ON  DECLARE @valuetosearchfor varchar(128), @objectOwner varchar(64)  SET @valuetosearchfor = '%staff%' --should be formatted as a like search   SET @objectOwner = 'dbo'    DECLARE @potentialcolumns TABLE (id int IDENTITY, sql varchar(4000))    INSERT INTO @potentialcolumns (sql)  SELECT       ('if exists (select 1 from [' +      [tabs].[table_schema] + '].[' +      [tabs].[table_name] +       '] (NOLOCK) where [' +       [cols].[column_name] +       '] like ''' + @valuetosearchfor + ''' ) print ''SELECT * FROM [' +      [tabs].[table_schema] + '].[' +      [tabs].[table_name] +       '] (NOLOCK) WHERE [' +       [cols].[column_name] +       '] LIKE ''''' + @valuetosearchfor + '''''' +      '''') as 'sql'  FROM information_schema.columns cols      INNER JOIN information_schema.tables tabs          ON cols.TABLE_CATALOG = tabs.TABLE_CATALOG              AND cols.TABLE_SCHEMA = tabs.TABLE_SCHEMA              AND cols.TABLE_NAME = tabs.TABLE_NAME  WHERE cols.data_type IN ('char', 'varchar', 'nvchar', 'nvarchar','text','ntext')      AND tabs.table_schema = @objectOwner      AND tabs.TABLE_TYPE = 'BASE TABLE'  ORDER BY tabs.table_catalog, tabs.table_name, cols.ordinal_position    DECLARE @count int  SET @count = (SELECT MAX(id) FROM @potentialcolumns)  PRINT 'Found ' + CAST(@count as varchar) + ' potential columns.'  PRINT 'Beginning scan...'  PRINT ''  PRINT 'These columns contain the values being searched for...'  PRINT ''  DECLARE @iterator int, @sql varchar(4000)  SET @iterator = 1  WHILE @iterator <= (SELECT Max(id) FROM @potentialcolumns)  BEGIN      SET @sql = (SELECT [sql] FROM @potentialcolumns where [id] = @iterator)      IF (@sql IS NOT NULL) and (RTRIM(LTRIM(@sql)) <> '')      BEGIN          --SELECT @sql --use when checking sql output          EXEC (@sql)      END      SET @iterator = @iterator + 1  END    PRINT ''  PRINT 'Scan completed'  


As others have said, you're likely going to have to write all the columns into your WHERE clause, either by hand or programatically. SQL does not include functionality to do it directly. A better question might be "why do you need to do this?". Needing to use this type of query is possibly a good indicator that your database isn't properly normalized. If you tell us your schema, we may be able to help with that problem too (if it's an actual problem).


I think you'd need to list all the columns in the where clause. I'm far from a SQL wizard though...maybe someone else knows a way.


you will have to write it out


Of course you have to write out all columns you want to use as a criteria.

If you add what programming language you are using and in what type of environment you working we can give you a clue or solution of how to do it dynamically.

I think your question really was how to do this dynamically depending of what the user of your program fill in in the "search"-form.. Im right?

If not, then.. Give us more information. ;)

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Next Post »