SQL Searching for Field Value - Value not Found - but we know it's there

The Mich
The Mich used Ask the Experts™
on
Trying to find the value in a field  - SQL Server 2008.     I know the value exists, yet my SearchAllTables stored procedure doesn't show any values.  

exec searchalltables '%Michelle%'
go


CREATE PROC SearchAllTables ( @SearchStr nvarchar(100) ) AS BEGIN

-- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
-- Purpose: To search all columns of all tables for a given search string
-- Written by: Narayana Vyas Kondreddi
-- Site: http://vyaskn.tripod.com + http://stackoverflow.com/questions/436351/how-do-i-find-a-value-anywhere-in-a-sql-server-database
-- Tested on: SQL Server 7.0 and SQL Server 2000
-- Date modified: 28th July 2002 22:50 GMT

DECLARE @Results TABLE(ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET  @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

WHILE @TableName IS NOT NULL
BEGIN
    SET @ColumnName = ''
    SET @TableName =
    (
        SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
        FROM    INFORMATION_SCHEMA.TABLES
        WHERE       TABLE_TYPE = 'BASE TABLE'
            AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
            AND OBJECTPROPERTY(
                    OBJECT_ID(
                        QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                         ), 'IsMSShipped'
                           ) = 0
    )

    WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
    BEGIN
        SET @ColumnName =
        (
            SELECT MIN(QUOTENAME(COLUMN_NAME))
            FROM    INFORMATION_SCHEMA.COLUMNS
            WHERE       TABLE_SCHEMA    = PARSENAME(@TableName, 2)
                AND TABLE_NAME  = PARSENAME(@TableName, 1)
                AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
                AND QUOTENAME(COLUMN_NAME) > @ColumnName
        )

        IF @ColumnName IS NOT NULL
        BEGIN
            INSERT INTO @Results
            EXEC
            (
                'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
                FROM ' + @TableName + ' (NOLOCK) ' +
                ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
            )
        END
    END
END

SELECT ColumnName, ColumnValue FROM @Results
END
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Qlemo"Batchelor", Developer and EE Topic Advisor
Top Expert 2015

Commented:
Check your stored procedure?! You are not showing what it does, so we cannot provide any help.
ste5anSenior Developer

Commented:
As we see, no results.. scnr.

Without your procedures code and data model and sample data, this is not really a solvable problem.

So sure, that you spelled it correctly? Maybe some part is case-sensitive? Do you use NO LOCK table hints?
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
try : exec searchalltables 'Michelle'

the procedure is also adding in the percent wildcard characters.

will look into it a bit more, but try that while I dig deeper.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
Hmmmmmm...... It works for me.

with or without the additional percent signs.

if you know that value is in a particular table, double check the table name et al using
select TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME, COLUMN_NAME, DATA_TYPE 
from information_schema.columns
where table_name = 'your table name'

Open in new window

Make sure you are in the right database (table_catalog) and the data_type is one of 'char', 'varchar', 'nchar', 'nvarchar'

Are you running multiple schema ?
Senior Developer
Commented:
Tst. The procedure is using the NOLOCK table hint... Thus the result can be wrong, e.g. skipping pages in reorg due to page splits.

Author

Commented:
I don't know what table the value is stored in - that's what I'm trying to find.    
I'm not sure about multiple schema as I don't know what that means.  
Should I remove the  '(NOLOCK)'?

The user created custom fields in a program and added values.   I'm pulling from the back end using SQL.    I can see the values in the program but my search doesn't find the value on the SQL server.    The search I referenced works - as it finds the user table w/my name in it, but we added that value (my name) to the custom fields and they don't appear.
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
Need to find that table - are you sure you have the right database ?

Isnt there a connection string or setup / ini file you could check ?

You could try adding NTEXT and TEXT to the type of columns

The other thing to search for is any unrecognised tables with XML datatypes.
select TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME, COLUMN_NAME, DATA_TYPE 
from information_schema.columns
where data_type = 'XML'

Open in new window

Unfortunately you cannot do a LIKE on an xml column, but could manually process by casting as varchar(max) and yes the nolock hint is pretty much never needed.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial