search for value in all tables sql

1DECLARE @SearchStr nvarchar(100)
2SET @SearchStr = '## YOUR STRING HERE ##'
3 
4 
5-- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
6-- Purpose: To search all columns of all tables for a given search string
7-- Written by: Narayana Vyas Kondreddi
8-- Site: http://vyaskn.tripod.com
9-- Updated and tested by Tim Gaunt
10-- http://www.thesitedoctor.co.uk
11-- http://blogs.thesitedoctor.co.uk/tim/2010/02/19/Search+Every+Table+And+Field+In+A+SQL+Server+Database+Updated.aspx
12-- Tested on: SQL Server 7.0, SQL Server 2000, SQL Server 2005 and SQL Server 2010
13-- Date modified: 03rd March 2011 19:00 GMT
14CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))
15 
16SET NOCOUNT ON
17 
18DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
19SET  @TableName = ''
20SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
21 
22WHILE @TableName IS NOT NULL
23 
24BEGIN
25    SET @ColumnName = ''
26    SET @TableName = 
27    (
28        SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
29        FROM     INFORMATION_SCHEMA.TABLES
30        WHERE         TABLE_TYPE = 'BASE TABLE'
31            AND    QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
32            AND    OBJECTPROPERTY(
33                    OBJECT_ID(
34                        QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
35                         ), 'IsMSShipped'
36                           ) = 0
37    )
38 
39    WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
40         
41    BEGIN
42        SET @ColumnName =
43        (
44            SELECT MIN(QUOTENAME(COLUMN_NAME))
45            FROM     INFORMATION_SCHEMA.COLUMNS
46            WHERE         TABLE_SCHEMA    = PARSENAME(@TableName, 2)
47                AND    TABLE_NAME    = PARSENAME(@TableName, 1)
48                AND    DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'int', 'decimal')
49                AND    QUOTENAME(COLUMN_NAME) > @ColumnName
50        )
51 
52        IF @ColumnName IS NOT NULL
53         
54        BEGIN
55            INSERT INTO #Results
56            EXEC
57            (
58                'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) FROM ' + @TableName + ' (NOLOCK) ' +
59                ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
60            )
61        END
62    END   
63END
64 
65SELECT ColumnName, ColumnValue FROM #Results
66 
67DROP TABLE #Results