Explanation:

While browsing the SQL Server newsgroups, every once in a while, I see a request for a script that can search all the columns of all the tables in a given database for a specific keyword. I never took such posts seriously. But then recently, one of my network administrators was troubleshooting a problem with Microsoft Operations Manager (MOM). MOM uses SQL Server for storing all the computer, alert and performance related information. He narrowed the problem down to something specific, and needed a script that can search all the MOM tables for a specific string. I had no such script handy at that time, so we ended up searching manually.

That's when I really felt the need for such a script and came up with this stored procedure "SearchAllTables". It accepts a search string as input parameter, goes and searches all char, varchar, nchar, nvarchar columns of all tables (only user created tables. System tables are excluded), owned by all users in the current database. Feel free to extend this procedure to search other datatypes.

The output of this stored procedure contains two columns:

(1) The table name and column name in which the search string was found 
( 2) The actual content/value of the column (Only the first 3630 characters are displayed)

Here's a word of caution, before you go ahead and run this procedure. Though this procedure is quite quick on smaller databases, it could take hours to complete, on a large database with too many character columns and a huge number of rows. So, if you are trying to run it on a large database, be prepared to wait (I did use the locking hint NOLOCK to reduce any locking). It is efficient to use Full-Text search feature for free text searching, but it doesn't make sense for this type of ad-hoc requirements.

Create this procedure in the required database and here is how you run it:

--To search all columns of all tables in Pubs database for the keyword "Computer"

EXEC SearchAllTables 'Computer'
GO

Here is the complete stored procedure code:

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