SQL-Search String '******' from all of tables which start from 'POP'

EXEC  csp_SearchString 'Customer1',  'POP'



Use below to search customer1 from all tables

EXEC  csp_SearchString 'Customer1',  ''




IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].csp_SearchString') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].csp_SearchString
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC  csp_SearchString(      @SearchStr nvarchar(100),  @TableName_LikeString varchar(100) ='',  @IsSelectString smallint =0 )
AS
BEGIN-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SET NOCOUNT ON
IF OBJECT_ID('tempdb..##CSTmp0001') IS NOT NULL
BEGIN
      DROP TABLE  ##CSTmp0001
END
CREATE TABLE ##CSTmp0001 (ColumnName nvarchar(370), ColumnValue nvarchar(max) , TblName nvarchar(370)  DEFAULT  '', ColName nvarchar(370)   DEFAULT  '', selectString varchar(max)  DEFAULT '',  DEX_ROW_ID  INT identity(1,1) )

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET  @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

WHILE @TableName IS NOT NULL
BEGIN
      SET @ColumnName = ''
     
      IF ISNULL(@TableName_LikeString,'') =''
      BEGIN
                  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
                  )
      END
      ELSE      
      BEGIN
                  SET @TableName =
                  (
                        SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
                        FROM INFORMATION_SCHEMA.TABLES
                        WHERE             TABLE_TYPE = 'BASE TABLE'     AND  TABLE_NAME LIKE   (  @TableName_LikeString+'%' )
                              AND   QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
                              AND   OBJECTPROPERTY(
                                          OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)  ), 'IsMSShipped'
                                                                                                        ) = 0
                  )
      END        

      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', 'text' )
                        AND   QUOTENAME(COLUMN_NAME) > @ColumnName
            )

            IF @ColumnName IS NOT NULL
            BEGIN
                  INSERT INTO ##CSTmp0001(ColumnName, ColumnValue, TblName,ColName )
                  EXEC
                  (
                  --    'SELECT  TOP  1  ''' + @TableName + '.' + @ColumnName + ''', Convert(nvarchar(max), ' + @ColumnName + ')
                        'SELECT  TOP  1    ''' + @TableName + '.' + @ColumnName + ''', Convert(nvarchar(max), ' + @ColumnName +')  ,   ''' + @TableName  + ''' ,   ''' + @ColumnName  + '''                                                
                        FROM ' + @TableName + ' (NOLOCK) ' +
                        ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
                  )
            END
      END  
END

UPDATE ##CSTmp0001
SET   selectString ='SELECT   *  FROM   '+TblName +'  WHERE  '+ColName +'  =  ''' + LTRIM(RTRIM(ColumnValue ))+'  '''

IF  ISNULL(@IsSelectString,0) =0
BEGIN
      SELECT ColumnName, ColumnValue,selectString   FROM ##CSTmp0001
END
ELSE
BEGIN
            Declare @TotalCounts as int ,@i  as int
            SET  @i =1
            SET  @TotalCounts =ISNULL(  ( SELECT  COUNT(*)  FROM  ##CSTmp0001  ),0)
            WHILE  @i <=@TotalCounts
            BEGIN
                        --SELECT ColumnName, ColumnValue,selectString   FROM ##CSTmp0001  WHERE DEX_ROW_ID =@i
                        declare @sql   as nvarchar(max)   ,@ParmDefinition  as nvarchar(2024)  ,@SqlConQuery as nvarchar(3000)
                        SET @ParmDefinition =N' @S as varchar(10)  ,  @dot as varchar(10) '
                        Set  @sql = ISNULL( ( SELECT   REPLACE( selectString , '*'  ,  'TableColumn = '''+ColumnName+' ''    ,   *   '    )       FROM  ##CSTmp0001  WHERE DEX_ROW_ID =@i  ), '' )
                        EXECUTE sp_executesql   @sql, @ParmDefinition, @S='' ,   @dot =','
                        --select  @sql
                        SET  @i = @i+1
            END                    
END
     
END------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
/**
Declare @StartTime as DateTime
SET   @StartTime  =getdate()
EXEC  csp_SearchString '09282010-AP40',  'POP'
SELECT   DATEDIFF( minute, @StartTime, getdate())  as 'Execute Minutes'

***/