The following query will create a stored procedure named SearchAllTables which will let you search all tables in the database for a value. 

1. 
   
2. CREATE OR ALTER PROC SearchAllTables
   
3. (
4.     @SearchStr nvarchar(100)
5. )
6. AS BEGIN
7.  
8.     CREATE TABLE #Results (
9.                         TableName                  nvarchar(70),
10.                         ColumnName              nvarchar(370),
11.                         ColumnValue              nvarchar(3630)
12.             )
13.  
14.     SET NOCOUNT ON
15.  
16.     DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
17.     SET @TableName = ''
18.     SET @SearchStr2 = QUOTENAME(@SearchStr, '''')
19.  
20.     WHILE @TableName IS NOT NULL BEGIN
21.         SET @ColumnName = ''
22.         SET @TableName =  (
23.             SELECT
24.                                                 MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
25.             FROM
26.                                                 INFORMATION_SCHEMA.TABLES
27.             WHERE 1=1
28.                                                 AND TABLE_TYPE = 'BASE TABLE'
29.                 AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
30.                 AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
31.         )
32.  
33.         WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL) BEGIN
34.             SET @ColumnName = (
35.                 SELECT
36.                                                             MIN(QUOTENAME(COLUMN_NAME))
37.                 FROM
38.                                                             INFORMATION_SCHEMA.COLUMNS
39.                 WHERE 1=1
40.                                                             AND TABLE_SCHEMA = PARSENAME(@TableName, 2)
41.                     AND TABLE_NAME    = PARSENAME(@TableName, 1)
42.                     AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'int', 'decimal', 'uniqueidentifier')
43.                     AND QUOTENAME(COLUMN_NAME) > @ColumnName
44.             )
45.  
46.             IF @ColumnName IS NOT NULL BEGIN
47.                 INSERT INTO
48.                                                             #Results
49.                 EXEC (''
50.                     + 'SELECT ''' + @TableName + ''', ''' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)'
51.                     + ' FROM ' + @TableName + ' (NOLOCK) '
52.                     + ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
53.                 )
54.             END
55.         END   
56.     END
57.  
58.     SELECT
59.                         TableName,
60.                         ColumnName,
61.                         ColumnValue,
62.                         COUNT(*) as Count,
63.                         Query = CONCAT('SELECT * FROM ', TableName, ' WHERE ', ColumnName, ' LIKE ', @SearchStr2)
64.             FROM
65.                         #Results
66.             GROUP BY
67.                         TableName, ColumnName,     ColumnValue
68.             ORDER BY
69.                         TableName, ColumnName, ColumnValue
70. END
    
71. 
    

1. EXEC SearchAllTables 'SearchTerm'