SQL Script - Search Text within database and find all tables having particular text

/****** Object:  StoredProcedure [dbo].[SearchAllTables]    Script Date: 26/09/2018 9:25:22 AM ******/
--Usage: EXECUTE [dbo].[SearchAllTables] 'SearchText'

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



CREATE PROC [dbo].[SearchAllTables]
(@SearchStr NVARCHAR(100))
AS
BEGIN

    DECLARE @Results TABLE
    (
        ColumnName NVARCHAR(370)
      , ColumnValue NVARCHAR(3630)
      , ssql VARCHAR(255)
    );

    SET NOCOUNT ON;

    DECLARE @TableName    NVARCHAR(256)
          , @ColumnName   NVARCHAR(128)
          , @ColumnName2  NVARCHAR(128)
          , @SearchStr2   NVARCHAR(110)
          , @SearchStrSQL NVARCHAR(MAX);

    SET @TableName = N'';
    SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%', '''');

    WHILE @TableName IS NOT NULL
    BEGIN
        SET @ColumnName = N'';
        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
        );
        PRINT @TableName;
        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
                SET @ColumnName2 = REPLACE(@ColumnName, '''', '');

                --SET @SearchStrSQL = 'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
                --             FROM ' + @TableName + ' (NOLOCK) ' +
                --             ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2

                -- PRINT @SearchStrSQL;

                INSERT INTO @Results
                EXEC ('SELECT DISTINCT ''' + @TableName + '.' + @ColumnName2 + ''', LEFT(' + @ColumnName + ', 3630)
, ''SELECT * FROM ' + @TableName + ' WHERE ' + @ColumnName2 + ' LIKE ''' + @SearchStr2 + '''''
                FROM ' + @TableName + ' (NOLOCK) ' + ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2);
            END;
        END;
    END;


    -- , ''SELECT * FROM ''' + @TableName + '.' + @ColumnName2 + ''' WHERE '' LEFT(' + @ColumnName + ', 3630) '' LIKE ' + @SearchStr2 + '''

    SELECT  ColumnName
          , ColumnValue
          , ssql
    FROM    @Results;
END;
GO