Searching and finding a string value in all columns in a SQL Server table

Problem

Sometimes there is a need to find if a string value exists in any column in your table.  Although there are system stored procedures that do a "for each database" or a "for each table", there is not a system stored procedure that does a "for each column".  So trying to find a value in any column in your database requires you to build the query to look through each column you want to search using an OR operator between each column.  Is there any way this can be dynamically generated?

Solution

Once again this is where T-SQL comes in handy along with the use of system tables or system views.  The code below allows you to search for a value in all text data type columns such as (char, nchar, ntext, nvarchar, text and varchar). 
The stored procedure gets created in the master database so you can use it in any of your databases and it takes three parameters:

• stringToFind - this is the string you are looking for.  This could be a simple value as 'test' or you can also use the % wildcard such as '%test%', '%test' or 'test%'.
• schema - this is the schema owner of the object
• table - this is the table name you want to search, the procedure will search all char, nchar, ntext, nvarchar, text and varchar columns in the table

The first thing you need to do is create this stored procedure by copying the below code and executing it in a query window.

USE master
GO
CREATE PROCEDURE sp_FindStringInTable @stringToFind VARCHAR(100), @schema sysname, @table sysname AS

DECLARE @sqlCommand VARCHAR(8000) DECLARE @where VARCHAR(8000) DECLARE @columnName sysname DECLARE @cursor VARCHAR(8000)
BEGIN TRY
   SET @sqlCommand = 'SELECT * FROM [' + @schema + '].[' + @table + '] WHERE'
   SET @where = ''

   SET @cursor = 'DECLARE col_cursor CURSOR FOR SELECT COLUMN_NAME
   FROM ' + DB_NAME() + '.INFORMATION_SCHEMA.COLUMNS
   WHERE TABLE_SCHEMA = ''' + @schema + '''
   AND TABLE_NAME = ''' + @table + '''
   AND DATA_TYPE IN (''char'',''nchar'',''ntext'',''nvarchar'',''text'',''varchar'')'

   EXEC (@cursor)

   OPEN col_cursor   
   FETCH NEXT FROM col_cursor INTO @columnName   

   WHILE @@FETCH_STATUS = 0   
   BEGIN   
       IF @where <> ''
           SET @where = @where + ' OR'

       SET @where = @where + ' [' + @columnName + '] LIKE ''' + @stringToFind + ''''
       FETCH NEXT FROM col_cursor INTO @columnName   
   END   

   CLOSE col_cursor   
   DEALLOCATE col_cursor 

   SET @sqlCommand = @sqlCommand + @where
   --PRINT @sqlCommand
   EXEC (@sqlCommand)  END TRY BEGIN CATCH
   PRINT 'There was an error. Check to make sure object exists.'
   IF CURSOR_STATUS('variable', 'col_cursor') <> -3
   BEGIN
       CLOSE col_cursor   
       DEALLOCATE col_cursor 
   END
END CATCH

Once the stored procedure has been created you can run some tests.
Here are some tests that were done against the AdventureWorks database.
Find the value 'Irv%' in the Person.Address table.

USE AdventureWorks
GO EXEC sp_FindStringInTable 'Irv%', 'Person', 'Address'

Find the value '%land%' in the Person.Address table.

USE AdventureWorks
GO EXEC sp_FindStringInTable '%land%', 'Person', 'Address'

Find the value '%land%' in the Person.Contact table.

USE AdventureWorks
GO EXEC sp_FindStringInTable '%land%', 'Person', 'Contact'

That's all there is to it.  Once this has been created you can use this against any table and any database on your server.