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

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
Michelle BlairAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Check your stored procedure?! You are not showing what it does, so we cannot provide any help.
0
ste5anSenior DeveloperCommented:
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?
0
Mark WillsTopic AdvisorCommented:
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.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Mark WillsTopic AdvisorCommented:
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 ?
0
ste5anSenior DeveloperCommented:
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Michelle BlairAuthor 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.
0
Mark WillsTopic AdvisorCommented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.