Search A Database For Text And Int Is Returning Error The Multi-part Identifier ... Could Not Be Bound

I have tried to modify and existing search all tables script that I found (in this answer from 2009) to include INT option, but I'm getting an error: Msg 4104, Level 16, State 1,

Solution 1:

When i am debugging/developing dynamic code i tend to use the print statement to see where my code goes wrong. I break away the procedure and then print the variable containing the dynamic sql. You could also use the debugger.

Whats happening is that in your dynamic code you are searching for a column which does not exist. This happens because you are missing extra quotes to convert into a string. Below is a print of the first bit of dynamic code when i use your procedure on the Northwind database and search for 'cat'.

if exists (SELECT name 
            FROM SYSOBJECTS 
            WHERE xtype ='U'-- SQL server sees [dbo].[Categories] as a non existing columnand name liketrim(']'fromSUBSTRING([dbo].[Categories] , 8, 100))) 
            BEGINSELECT'[dbo].[Categories].[CategoryID]', LEFT([CategoryID], 3630) 
                FROM [dbo].[Categories] (NOLOCK)  WHERE 
                [CategoryID] =1ENDELSEBEGINSELECT'[dbo].[Categories].[CategoryID]', LEFT([CategoryID], 3630) 
                FROM [dbo].[Categories] (NOLOCK)  WHERE [CategoryID] LIKE'%cat%'END

Below are two changes to your code to make it work. Extra quotes and an extra variable which strips away the schema since the schema is not present in the name column of the table you are looking in.

ALTER PROC [dbo].[SearchAllTables]
(
    @SearchStr nvarchar(100),
    @SearchIntint=0
)
ASBEGIN-- 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-- Tested on: SQL Server 7.0 and SQL Server 2000-- Date modified: 28th July 2002 22:50 GMTDECLARE@ResultsTABLE(ColumnName nvarchar(370), ColumnValue nvarchar(3630))

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

WHILE @TableNameISNOTNULLBEGINSET@ColumnName=''SET@TableName= 
    (
        SELECTMIN(QUOTENAME(TABLE_SCHEMA) +'.'+ QUOTENAME(TABLE_NAME))
        FROM    INFORMATION_SCHEMA.TABLES
        WHERE       TABLE_TYPE ='BASE TABLE'AND QUOTENAME(TABLE_SCHEMA) +'.'+ QUOTENAME(TABLE_NAME) >@TableNameAND OBJECTPROPERTY(
                    OBJECT_ID(
                        QUOTENAME(TABLE_SCHEMA) +'.'+ QUOTENAME(TABLE_NAME)
                         ), 'IsMSShipped'
                           ) =0
    )
    -- Since the table used below in the dynamic SQL does not have a prepended schema is strip it hereSET@TableNoSchema=SUBSTRING(@TableName, CHARINDEX('.',@TableName) +2, LEN(@TableName) - CHARINDEX('.',@TableName) -2 )

    WHILE (@TableNameISNOTNULL) AND (@ColumnNameISNOTNULL)
    BEGINSET@ColumnName=
        (
            SELECTMIN(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', 'int')
                AND QUOTENAME(COLUMN_NAME) >@ColumnName
        )

        IF @ColumnNameISNOTNULLBEGININSERTINTO@ResultsEXEC-- only added extra quotes so SQL server sees a string instead of a column
            (
                'if exists (SELECT name FROM SYSOBJECTS WHERE xtype = ''U'' and name like trim('']'' from SUBSTRING('''+@TableNoSchema+''' , 8, 100))) BEGIN '+'SELECT '''+@TableName+'.'+@ColumnName+''', LEFT('+@ColumnName+', 3630) 
                FROM '+@TableName+' (NOLOCK) '+' WHERE '+@ColumnName+' = '+@SearchInt+' END
               ELSE
                BEGIN '+'SELECT '''+@TableName+'.'+@ColumnName+''', LEFT('+@ColumnName+', 3630) 
                FROM '+@TableName+' (NOLOCK) '+' WHERE '+@ColumnName+' LIKE '+@SearchStr2+' END'
            )
        ENDENDENDSELECT ColumnName, ColumnValue FROM@ResultsEND