Reputation: 1629

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?

Upvotes: 0

Answers (5)

StingyJack

Reputation: 19469

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'

Upvotes: 0

rmeador

Reputation: 25704

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).

Upvotes: 3

Stefan

Reputation: 11509

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. ;)

Upvotes: 0