Search a database for text and int is returning error The multi-part identifier ... could not be bound

Question

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, Line 1
The multi-part identifier "dbo.ADACS_MAPS" could not be bound.

for every single column in the database when I run it.

ALTER PROC [dbo].[SearchAllTables]
(
    @SearchStr nvarchar(100),
    @SearchInt int = 0
)
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
-- 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', 'int')
                AND QUOTENAME(COLUMN_NAME) > @ColumnName
        )

        IF @ColumnName IS NOT NULL
        BEGIN
            INSERT INTO @Results
            EXEC
            (
                'if exists (SELECT name FROM SYSOBJECTS WHERE xtype = ''U'' and name like trim('']'' from SUBSTRING(' + @TableName + ' , 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'
            )
        END
    END 
END

SELECT ColumnName, ColumnValue FROM @Results
END

Can somebody please point out what I'm doing wrong, as this (I'm not ashamed to admit) is beyond my knowledge.

You should be able to run this on any DB, so I have not included demo data.

Edit 1:

The only parts I have modified is:

1. added the param
2. changed the final select from:

EXEC
   (
      'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 
      FROM ' + @TableName + ' (NOLOCK) ' +
      ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
   )

to what it is now.

Edit 2:

Found a solution here that works well, but I will work on my script to see why its not working.

Thanks to the first 2 commentators for the assist, I will look at that code a little more closely using the select as suggested.

Answer 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 column
            and name like trim(']' from SUBSTRING([dbo].[Categories] , 8, 100))) 
            BEGIN 
            SELECT '[dbo].[Categories].[CategoryID]', LEFT([CategoryID], 3630) 
                FROM [dbo].[Categories] (NOLOCK)  WHERE 
                [CategoryID] = 1 END
               ELSE
                BEGIN SELECT '[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),
    @SearchInt int = 0
)
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
-- 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), @TableNoSchema nvarchar(256)
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
    )
    -- Since the table used below in the dynamic SQL does not have a prepended schema is strip it here
    SET @TableNoSchema = SUBSTRING(@TableName, CHARINDEX('.',@TableName) + 2, LEN(@TableName) - CHARINDEX('.',@TableName) -2 )

    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', 'int')
                AND QUOTENAME(COLUMN_NAME) > @ColumnName
        )

        IF @ColumnName IS NOT NULL
        BEGIN
            INSERT INTO @Results
            EXEC -- 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'
            )
        END
    END 
END

SELECT ColumnName, ColumnValue FROM @Results
END