SET @schema = 'Person'   
SET @TableName = 'Person'
SET @Value = 'Xu%'
SET @ColumnNames = 'FirstName,LastName'	-- can be empty or * for ALL columns

/* 
CREATE PROCEDURE [dbo].[FindValue] 
   @TableName NVARCHAR(128), /* Must be a valid table or view name,  
                               must not be quoted or contain a schema*/ 
   @Value NVARCHAR(4000), /*May contain wildcards*/  
   @schema NVARCHAR(128) = 'dbo' /*May be left out*/ 
AS 

Sample Execution 

Exec FindValue  
   @TableName = 'spt_monitor', 
   @Value = '8', 
   @schema = 'dbo' 
*/ 

/* 
If given a string it will finds all rows where any char, varchar, or  
their Unicode equivalent which contain that string in the selected  
table or view.  Note that this only works on objects which have entries  
in information_schema.columns, which excludes certain system objects. If  
given a numeric value it will check those text types for a match as well  
as numeric types.  If given a possible date, it will also check date type.   
The string that is being searched for may contain wildcard characters such as %.   
This will NOT search text, ntext, xml, or user defined fields.  This may  
return a row more than once if the search string is found in more than one  
column in that row. 
*/ 
DECLARE
   @TableName NVARCHAR(128), /* Must be a valid table or view name,  
                               must not be quoted or contain a schema*/ 
   @Value NVARCHAR(4000), /*May contain wildcards*/  
   @schema NVARCHAR(128) /*May be left out*/ 
   ,@ColumnNames NVARCHAR(4000)	-- list of columns to search for, can be * for ALL

SET @schema = 'Person'   
SET @TableName = 'Person'
SET @Value = 'Xu%'
SET @ColumnNames = 'FirstName,LastName'	-- can be empty or * for ALL columns
SET @ColumnNames = REPLACE(@ColumnNames, ' ', '')	-- removes all space
   
/**************************** Declare Variables ***********************/ 
DECLARE @columns TABLE     (ColumnName NVARCHAR(128)) 
DECLARE @columnsFiltered TABLE     (ColumnName NVARCHAR(128)) 

DECLARE @sql NVARCHAR(MAX) 

/************************** Populate Table Variable *****************/ 
/*Takes the names of string type columns for the selected table */ 
INSERT INTO @columns 
   (ColumnName) 
SELECT  
   Column_name  
FROM  
   INFORMATION_SCHEMA.COLUMNS 
WHERE 
   Table_schema = @schema 
   AND Table_name = @TableName 
   AND data_type IN ('char', 'nchar', 'varchar', 'nvarchar') 
    
/* If it is numeric, also check the numeric fields */ 
IF ISNUMERIC(@value) = 1 
   INSERT INTO @columns 
       (ColumnName) 
   SELECT  
       Column_name  
   FROM  
       INFORMATION_SCHEMA.COLUMNS 
   WHERE 
       Table_schema = @schema 
       AND Table_name = @TableName 
       AND data_type IN ('int', 'numeric', 'bigint', 'money',  
                           'smallint', 'smallmoney',  
                           'tinyint', 'float', 'decimal', 'real') 
                            
IF ISDATE(@value) = 1 
   INSERT INTO @columns 
       (ColumnName) 
   SELECT  
       Column_name  
   FROM  
       INFORMATION_SCHEMA.COLUMNS 
   WHERE 
       Table_schema = @schema 
       AND Table_name = @TableName 
       AND data_type IN ('datetime', 'smalldatetime') 

INSERT INTO @columnsFiltered
SELECT ColumnName
FROM @columns
WHERE 
	(@ColumnNames IN ('*','')
	OR
	CHARINDEX(',' + ColumnName + ',', ',' + @ColumnNames + ',') > 0
	)

/********************* Prepare dynamic SQL Statement to Execute **********/ 
SELECT  
   @sql =  
       CASE  
           WHEN @sql IS NULL  
               THEN 'Select ''' + ColumnName  
                   + ''' as ContainingColumn, * From '  
                   + QUOTENAME(@Schema) + '.' + QUOTENAME(@TableName)  
                   + ' where ' + ColumnName + ' like ''' + @Value + ''' '   
           WHEN @sql IS NOT NULL  
               THEN @sql + 'UNION ALL Select ''' + ColumnName  
                   + ''' as ContainingColumn, * From ' + QUOTENAME(@Schema)  
                   + '.' + QUOTENAME(@TableName)  
                   + ' where ' + ColumnName + ' like ''' + @Value + ''' '   
       END  
FROM 
   @columnsFiltered 

/******************* Execute Statement and display results ***********/ 
--print @sql /* This may be uncommented for testing purposes */ 
EXEC (@sql) 

WHERE '411-555-1212' IN (HomePhone, WorkPhone, CellPhone, AltPhone)

WHERE (HomePhone+'|'+WorkPhone+'|'+CellPhone+'|'+AltPhone) like '%555-1212%'