Find a string by searching all tables in SQL Server

Is there any way to search for a string in all tables of a database in SQL Server?

I want to search for string say john. The result should show the tables and their respective row that contain john.

If you are like me and have certain restrictions in a production environment, you may wish to use a table variable instead of temp table, and an ad-hoc query rather than a create procedure.

Of course depending on your sql server instance, it must support table variables.

I also added a USE statement to narrow the search scope

USE DATABASE_NAME
DECLARE @SearchStr nvarchar(100) = 'SEARCH_TEXT'
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', 'decimal')
                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

A bit late but hopefully useful.

Why not try some of the third party tools that can be integrated into SSMS.

I’ve worked with ApexSQL Search (100% free) with good success for both schema and data search and there is also SSMS tools pack that has this feature (not free for SQL 2012 but quite affordable).

Stored procedure above is really great; it’s just that this is way more convenient in my opinion. Also, it would require some slight modifications if you want to search for datetime columns or GUID columns and such…

To update TechDo's answer for SQL server 2012. You need to change: 'FROM ' + @TableName + ' (NOLOCK) ' to FROM ' + @TableName + 'WITH (NOLOCK) ' +

Other wise you will get the following error: Deprecated feature 'Table hint without WITH' is not supported in this version of SQL Server.

Below is the complete updated stored procedure:

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

	CREATE TABLE #Results (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', 'decimal')
					AND    QUOTENAME(COLUMN_NAME) > @ColumnName
			)

			IF @ColumnName IS NOT NULL

			BEGIN
				INSERT INTO #Results
				EXEC
				(
					'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 
					FROM ' + @TableName + 'WITH (NOLOCK) ' +
					' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
				)
			END
		END    
	END

	SELECT ColumnName, ColumnValue FROM #Results
END

A bit late, but you can easily find a string with this query

DECLARE
@search_string  VARCHAR(100),
@table_name     SYSNAME,
@table_id       INT,
@column_name    SYSNAME,
@sql_string     VARCHAR(2000)

SET @search_string = 'StringtoSearch'

DECLARE tables_cur CURSOR FOR SELECT ss.name +'.'+ so.name [name], object_id FROM sys.objects so INNER JOIN sys.schemas ss ON so.schema_id = ss.schema_id WHERE  type = 'U'

OPEN tables_cur

FETCH NEXT FROM tables_cur INTO @table_name, @table_id

WHILE (@@FETCH_STATUS = 0)
BEGIN
    DECLARE columns_cur CURSOR FOR SELECT name FROM sys.columns WHERE object_id = @table_id 
        AND system_type_id IN (167, 175, 231, 239)

    OPEN columns_cur

    FETCH NEXT FROM columns_cur INTO @column_name
        WHILE (@@FETCH_STATUS = 0)
        BEGIN
            SET @sql_string = 'IF EXISTS (SELECT * FROM ' + @table_name + ' WHERE [' + @column_name + '] 
            LIKE ''%' + @search_string + '%'') PRINT ''' + @table_name + ', ' + @column_name + ''''

            EXECUTE(@sql_string)

        FETCH NEXT FROM columns_cur INTO @column_name
        END

    CLOSE columns_cur

DEALLOCATE columns_cur

FETCH NEXT FROM tables_cur INTO @table_name, @table_id
END

CLOSE tables_cur
DEALLOCATE tables_cur

There's no need for nested looping (outer looping through tables and inner looping through all table columns). One can retrieve all (or arbitrary selected/filtered) table-column combinations from INFORMATION_SCHEMA.COLUMNS and in one loop simply pass through (search) all of them:

DECLARE @search VARCHAR(100), @table SYSNAME, @column SYSNAME

DECLARE curTabCol CURSOR FOR
    SELECT c.TABLE_SCHEMA + '.' + c.TABLE_NAME, c.COLUMN_NAME
    FROM INFORMATION_SCHEMA.COLUMNS c
    JOIN INFORMATION_SCHEMA.TABLES t 
      ON t.TABLE_NAME=c.TABLE_NAME AND t.TABLE_TYPE='BASE TABLE' -- avoid views
    WHERE c.DATA_TYPE IN ('varchar','nvarchar') -- searching only in these column types
    --AND c.COLUMN_NAME IN ('NAME','DESCRIPTION') -- searching only in these column names

SET @search='john'

OPEN curTabCol
FETCH NEXT FROM curTabCol INTO @table, @column

WHILE (@@FETCH_STATUS = 0)
BEGIN
    EXECUTE('IF EXISTS 
             (SELECT * FROM ' + @table + ' WHERE ' + @column + ' = ''' + @search + ''') 
             PRINT ''' + @table + '.' + @column + '''')
    FETCH NEXT FROM curTabCol INTO @table, @column
END

CLOSE curTabCol
DEALLOCATE curTabCol

I have written a SP for the this which returns the search results in form of Table name, the Column names in which the search keyword string was found as well as the searches the corresponding rows as shown in below screen shot.

This might not be the most efficient solution but you can always modify and use it according to your need.

IF OBJECT_ID('sp_KeywordSearch', 'P') IS NOT NULL
	DROP PROC sp_KeywordSearch
GO

CREATE PROCEDURE sp_KeywordSearch @KeyWord NVARCHAR(100)
AS
BEGIN
	DECLARE	@Result TABLE
		(TableName NVARCHAR(300),
		 ColumnName NVARCHAR(MAX))

	DECLARE	@Sql NVARCHAR(MAX),
		@TableName NVARCHAR(300),
		@ColumnName NVARCHAR(300),
		@Count INT

	DECLARE	@tableCursor CURSOR

	SET @tableCursor = CURSOR LOCAL SCROLL FOR
	SELECT	N'SELECT @Count = COUNT(1) FROM [dbo].[' + T.TABLE_NAME + '] WITH (NOLOCK) WHERE CAST([' + C.COLUMN_NAME +
			'] AS NVARCHAR(MAX)) LIKE ''%' + @KeyWord + N'%''',
			T.TABLE_NAME,
			C.COLUMN_NAME
	FROM	INFORMATION_SCHEMA.TABLES AS T WITH (NOLOCK)
	INNER JOIN INFORMATION_SCHEMA.COLUMNS AS C WITH (NOLOCK)
	ON		T.TABLE_SCHEMA = C.TABLE_SCHEMA AND
			T.TABLE_NAME = C.TABLE_NAME
	WHERE	T.TABLE_TYPE = 'BASE TABLE' AND
			C.TABLE_SCHEMA = 'dbo' AND
			C.DATA_TYPE NOT IN ('image', 'timestamp')

	OPEN @tableCursor
	FETCH NEXT FROM @tableCursor INTO @Sql, @TableName, @ColumnName

	WHILE (@@FETCH_STATUS = 0)
	BEGIN
		SET @Count = 0

		EXEC sys.sp_executesql
			@Sql,
			N'@Count INT OUTPUT',
			@Count OUTPUT
	
		IF @Count > 0
		BEGIN
			INSERT	INTO @Result
					(TableName, ColumnName)
			VALUES	(@TableName, @ColumnName)
		END

		FETCH NEXT FROM @tableCursor INTO @Sql, @TableName, @ColumnName
	END

	CLOSE @tableCursor
	DEALLOCATE @tableCursor

	SET @tableCursor = CURSOR LOCAL SCROLL FOR
	SELECT	SUBSTRING(TB.Sql, 1, LEN(TB.Sql) - 3) AS Sql, TB.TableName, SUBSTRING(TB.Columns, 1, LEN(TB.Columns) - 1) AS Columns
	FROM	(SELECT R.TableName, (SELECT R2.ColumnName + ', ' FROM @Result AS R2 WHERE R.TableName = R2.TableName FOR XML PATH('')) AS Columns,
					'SELECT * FROM ' + R.TableName + ' WITH (NOLOCK) WHERE ' +
					(SELECT	'CAST(' + R2.ColumnName + ' AS NVARCHAR(MAX)) LIKE ''%' + @KeyWord + '%'' OR '
					 FROM	@Result AS R2
					 WHERE	R.TableName = R2.TableName
					FOR
					 XML PATH('')) AS Sql
			 FROM	@Result AS R
			 GROUP BY R.TableName) TB
	ORDER BY TB.Sql

	OPEN @tableCursor
	FETCH NEXT FROM @tableCursor INTO @Sql, @TableName, @ColumnName

	WHILE (@@FETCH_STATUS = 0)
	BEGIN
		PRINT @Sql
		SELECT	@TableName AS [Table],
				@ColumnName AS Columns
		EXEC(@Sql)

		FETCH NEXT FROM @tableCursor INTO @Sql, @TableName, @ColumnName
	END

	CLOSE @tableCursor
	DEALLOCATE @tableCursor

END

Improving the amazing answer from @Brandon, I added type to ntext and xml using castings:

BEGIN TRAN

DECLARE @SearchStr nvarchar(100) = 'SEARCH_TEXT'
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', 'decimal', 'ntext', 'xml')
                AND    QUOTENAME(COLUMN_NAME) > @ColumnName
        )

        IF @ColumnName IS NOT NULL

        BEGIN
            INSERT INTO @Results
            EXEC
            (
                'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT((cast(' + @ColumnName + ' as nvarchar(max))), 3630) 
                FROM ' + @TableName + ' (NOLOCK) ' +
                ' WHERE (cast(' + @ColumnName + ' as nvarchar(max))) LIKE ' + @SearchStr2
            )
        END
    END    
END

SELECT ColumnName, ColumnValue FROM @Results

ROLLBACK

This was very helpful. I wanted to import this function to a Postgre SQL database. Thought i would share it with anyone who is interested. Will have them a few hours. Note: this function creates a list of SQL statements that can be copied and executed on the Postgre database. Maybe someone smarter then me can get Postgre to create and execute the statements all in one function.

CREATE OR REPLACE FUNCTION SearchAllTables(_search text) RETURNS TABLE( txt text ) as $funct$
	DECLARE __COUNT int;
	__SQL text;
BEGIN
	EXECUTE 'SELECT COUNT(0) FROM INFORMATION_SCHEMA.COLUMNS
					WHERE    DATA_TYPE = ''text'' 
					AND  		 table_schema = ''public'' ' INTO __COUNT;
					
	RETURN QUERY 
		SELECT CASE WHEN ROW_NUMBER() OVER (ORDER BY table_name) < __COUNT THEN 
			'SELECT ''' || table_name ||'.'|| column_name || ''' AS tbl, "'  || column_name || '" AS col FROM "public"."' || "table_name" || '" WHERE "'|| "column_name" || '" ILIKE ''%' || _search  || '%'' UNION ALL' 
			ELSE 
			'SELECT ''' || table_name ||'.'|| column_name || ''' AS tbl, "'  || column_name || '" AS col FROM "public"."' || "table_name" || '" WHERE "'|| "column_name" || '" ILIKE ''%' || _search  || '%'''
		END AS txt
					
					FROM     INFORMATION_SCHEMA.COLUMNS
					WHERE    DATA_TYPE = 'text' 
					AND  		 table_schema = 'public';
END
$funct$ LANGUAGE plpgsql;

The answer that was mentioned in this post already several times I have adopted a little bit because I needed to search in only one table too:

(and also made input for the table name a bit more simpler)

ALTER PROC dbo.db_compare_SearchAllTables_sp
(
	@SearchStr nvarchar(100),
	@TableName nvarchar(256) = ''
)
AS
BEGIN
		if PARSENAME(@TableName, 2) is null
			set @TableName = 'dbo.' + QUOTENAME(@TableName, '"')

    declare @results TABLE(ColumnName nvarchar(370), ColumnValue nvarchar(3630))

    SET NOCOUNT ON

    DECLARE @ColumnName nvarchar(128) = '', @SearchStr2 nvarchar(110)
    SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
		IF @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
                INSERT INTO @results
                EXEC
                (
                    'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 
                    FROM ' + @TableName + ' WITH (NOLOCK) ' +
                    ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
                )
            END
        END    
		ELSE
    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 + ' WITH (NOLOCK) ' +
                    ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
                )
            END
        END    
    END

    SELECT ColumnName, ColumnValue FROM @results
END

[CREATE PROC SearchAllTables ( @SearchStr nvarchar(100) ) AS 
        BEGIN 
            
        CREATE TABLE #Results (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
    
    EXEC SearchAllTables 'Computer'][1]


  [1]: https://koukia.ca/search-for-a-string-in-all-tables-and-all-fields-of-a-database-with-tsql-b6f8a1bfda37

Not to take away from the excellent answer by @Brandon but the OP requested "The result should show the tables and their respective row". I created a record-level solution based on the original answer from @Brandon that also avoids the use of CURSORs used by others here by simply concatenating all the appropriate columns into one string. (I have the feeling this would perform better but I honestly haven't compared results - feel free to do so and provide feedback here.)

NB I have used the STRING_AGG function to concatenate the columns. If you have an older version of SQL Server you may need to use one of the alternate methods that can be found online.

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

    SET NOCOUNT ON

    DECLARE @TableName NVARCHAR(256), @SearchStr2 NVARCHAR(110)
	DECLARE @ColumnList NVARCHAR(2048), @ColumnJoin NVARCHAR(2048)
    SET  @TableName = ''
    SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

    WHILE @TableName IS NOT NULL
    BEGIN
            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	-- For progress monitoring
			SET @ColumnList = 
			(
				SELECT STRING_AGG(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')
			)
			SET @ColumnJoin = REPLACE(@ColumnList,',','+')
			
			IF @ColumnList <> '' 
				EXEC
				(
						'SELECT ''' + @TableName + ''' AS [Table],' + @ColumnList + '
						INTO #Results
						FROM ' + @TableName + ' (NOLOCK) ' +
						' WHERE ' + @ColumnJoin + ' LIKE ' + @SearchStr2 + '
						
						IF @@ROWCOUNT > 0 SELECT * FROM #Results'
				)
    END
END

There are lots of workable answers already. Just thought I would add one I came up with that has a lot of optional funcionality.

--=======================================================================
--	MSSQL Unified Search
--	Minimum compatibility level = 130 (SQL Server 2016)
--		NOTE: The minimum compatibility level is required by the built-in STRING_SPLIT() function.
--			However, you can create the STRING_SPLIT() function at the bottom of this script for
--			lower versions of MSSQL Server.
--
--	Usage:
--		Set the parameters below and execute this script.
--
/************************ Enter Parameters Here ************************/
/**/
/**/	DECLARE @SearchString VARCHAR(1000) = 'value to search for';  -- Accepts SQL wilcards
/**/
/**/	DECLARE @IncludeUserTables BIT = 1;
/**/	DECLARE @IncludeViews BIT = 0;
/**/	DECLARE @IncludeStoredProcedures BIT = 0;
/**/	DECLARE @IncludeFunctions BIT = 0;
/**/	DECLARE @IncludeTriggers BIT = 0;
/**/
/**/	DECLARE @DebugMode BIT = 0;
/**/	DECLARE @ExcludeColumnTypes NVARCHAR(500) = 'text, ntext, char, nchar, timestamp, bigint, tinyint, smallint, bit, date, time, smalldatetime, datetime, datetime2, real, money, float, decimal, binary, varbinary, image';  -- Comma delimited list
/**/
/***********************************************************************/


SET NOCOUNT ON;
SET @SearchString = QUOTENAME(@SearchString,'''');

DECLARE @Results TABLE ([ObjectType] NVARCHAR(200), [ObjectName] NVARCHAR(200), [ColumnName] NVARCHAR(400), [Value] NVARCHAR(MAX), [SelectStatement] NVARCHAR(1000));
DECLARE @ExcludeColTypes TABLE (system_type_id INT);

INSERT INTO @ExcludeColTypes ([system_type_id])
	SELECT [system_type_id]
	FROM sys.types WHERE
	[name] IN (
		SELECT LTRIM(RTRIM([value])) FROM STRING_SPLIT(@ExcludeColumnTypes,',')
		);

DECLARE @ObjectType NVARCHAR(200);
DECLARE @ObjectName NVARCHAR(200);
DECLARE @Value NVARCHAR(MAX);
DECLARE @SelectStatement NVARCHAR(1000);
DECLARE @Query NVARCHAR(4000);


/********************* Table Objects *********************/
IF (@IncludeUserTables = 1)
BEGIN
	DECLARE @TableObjectId INT = (SELECT MIN([object_id]) FROM sys.tables);
	DECLARE @ColumnId INT;
	WHILE @TableObjectId IS NOT NULL
	BEGIN
	
		SELECT @ObjectType = 'USER TABLE';
		SELECT @ObjectName = '[' + SCHEMA_NAME([schema_id]) + '].[' + OBJECT_NAME(@TableObjectId) + ']' FROM sys.tables WHERE [object_id] = @TableObjectId;

		SET @ColumnId = (SELECT MIN([column_id]) FROM sys.columns WHERE [system_type_id] NOT IN (SELECT [system_type_id] FROM @ExcludeColTypes) AND [object_id] = @TableObjectId);
		WHILE @ColumnId IS NOT NULL
		BEGIN

			SELECT @Value = '[' + [name] +']' FROM sys.columns WHERE [object_id] = @TableObjectId AND column_id = @ColumnId;

			SET @SelectStatement = 'SELECT * FROM ' + @ObjectName + ' WHERE CAST(' + @Value + ' AS NVARCHAR(4000)) LIKE ' + @SearchString + ';';

			SET @Query = 'SELECT '
				+ QUOTENAME(@ObjectType, '''')
				+ ', ' + QUOTENAME(@ObjectName, '''')
				+ ', ' + QUOTENAME(@Value, '''')
				+ ', ' + @Value
				+ ', ''' + REPLACE(@SelectStatement,'''','''''') + ''''
				+ ' FROM ' + @ObjectName
				+ ' WHERE CAST(' + @Value + ' AS NVARCHAR(4000)) LIKE ' + @SearchString + ';';

			IF @DebugMode = 0
			BEGIN
				INSERT INTO @Results EXEC(@Query);
			END;
			ELSE
			BEGIN
				PRINT 'Select Statement:  ' + @SelectStatement;
				PRINT 'Query:  ' + @Query;
			END;

			SET @ColumnId = (SELECT MIN([column_id]) FROM sys.columns WHERE [system_type_id] NOT IN (SELECT [system_type_id] FROM @ExcludeColTypes) AND [object_id] = @TableObjectId AND [column_id] > @ColumnId);
		END;

		SET @TableObjectId = (SELECT MIN([object_id]) FROM sys.tables WHERE [object_id] > @TableObjectId);
	END;
END;

IF @DebugMode = 0
BEGIN
	SELECT 
		[ObjectType]
		,[ObjectName]
		,[ColumnName]
		,[Value]
		,[Count] = CASE
			WHEN [ObjectType] IN ('USER TABLE') THEN COUNT(1)
			ELSE NULL
		END
		,[SelectStatement]
	FROM @Results
	GROUP BY [ObjectType], [ObjectName], [ColumnName], [Value], [SelectStatement]
	ORDER BY [Value];
END;

/********************* Objects Other than Tables *********************/
SET @Query = 'SELECT ' +
	'ObjectType = CASE ' +
		'WHEN b.[type] = ''V'' THEN ''VIEW'' ' +
		'WHEN b.[type] = ''P'' THEN ''STORED PROCEDURE'' ' +
		'WHEN b.[type] = ''FN'' THEN ''SCALAR-VALUED FUNCTION'' ' +
		'WHEN b.[type] = ''IF'' THEN ''TABLE-VALUED FUNCTION'' ' +
		'WHEN b.[type] = ''TR'' THEN ''TRIGGER'' ' +
	'END ' +
	',[ObjectName] = ''['' + SCHEMA_NAME(b.[schema_id]) + ''].['' + OBJECT_NAME(a.[object_id]) + '']'' ' +
	',[ColumnName] = NULL ' +
	',[Value] = a.[definition] ' +
	',[SelectStatement] = ''SP_HELPTEXT '' + QUOTENAME(''['' + SCHEMA_NAME(b.[schema_id]) + ''].['' + OBJECT_NAME(a.[object_id]) + '']'','''''''') + '';'' ' +
'FROM [sys].[sql_modules] a ' +
'JOIN [sys].[objects] b ON a.[object_id] = b.[object_id] ' +
'WHERE ' +
	'( ' +
	'	a.[definition] LIKE ' + @SearchString + 
	') ' +
	'AND ' +
	'( ' +
	'	( ' +
			CAST(@IncludeViews AS VARCHAR(1)) + ' = 1 ' +
	'		AND ' +
	'		b.[type] IN (''V'') ' +
	'	) ' +
	'	OR ' +
	'	( ' +
			CAST(@IncludeStoredProcedures AS VARCHAR(1)) + ' = 1 ' +
	'		AND ' +
	'		b.[type] IN (''P'') ' +
	'	) ' +
	'	OR ' +
	'	( ' +
			CAST(@IncludeFunctions AS VARCHAR(1)) + ' = 1 ' +
	'		AND ' +
	'		b.[type] IN (''FN'',''IF'') ' +
	'	) ' +
	'	OR ' +
	'	( ' +
			CAST(@IncludeTriggers AS VARCHAR(1)) + ' = 1 ' +
	'		AND ' +
	'		b.[type] IN (''TR'') ' +
	'	) ' +
	'); ';

IF @DebugMode = 0
BEGIN
	INSERT INTO @Results EXEC(@Query);
END;
ELSE
BEGIN
	PRINT 'Select Statement:  ' + @SelectStatement;
	PRINT 'Query:  ' + @Query;
END;


/********************** STRING_SPLIT() FUNCTION **********************

CREATE FUNCTION STRING_SPLIT (
    @Expression nvarchar(4000)
    ,@Delimiter nvarchar(100)
)
RETURNS @Ret TABLE ([value] NVARCHAR(4000))
AS
BEGIN

	DECLARE @Start INT = 0, @End INT, @Length INT;
	SELECT @End = CHARINDEX(@Delimiter,@Expression), @Length = @End - @Start;

	IF @End <= 0
	BEGIN
		INSERT INTO @Ret ([value]) VALUES (@Expression);
	END
	ELSE
	BEGIN
		WHILE @Length >= 0
		BEGIN
			INSERT INTO @Ret ([value])
				SELECT ltrim(rtrim(substring(@Expression,@Start,@Length)));
	
			SELECT @Start = @End + LEN(@Delimiter)
			SELECT @End = CHARINDEX(@Delimiter,@Expression,@Start)
			IF @End < 1
				SELECT @End = LEN(@Expression) + 1;
			SELECT @Length = @End - @Start;
	
		END;
	END;
	RETURN;
END;

*********************************************************************/