Stored Procedure to search a string in
1. Tables like
2. In Columns (upto 9)

CREATEPROCEDURE [dbo].[zz_spSearchForValueInColumns_And_TablesLike]
@SearchString AS VARCHAR(255)
,@TableNameLike nvarchar(256)
,@Column1 nvarchar(256)
,@Column2 nvarchar(256) = NULL
,@Column3 nvarchar(256)=NULL
,@Column4 nvarchar(256)=NULL
,@Column5 nvarchar(256)=NULL
,@Column6 nvarchar(256)=NULL
,@Column7 nvarchar(256)=NULL
,@Column8 nvarchar(256)=NULL
,@Column9 nvarchar(256)=NULL
AS

/*variables*/
DECLARE @error int
Declare @SQuery varchar(MAX)

SET @TableNameLike='''%' + @TableNameLike+''''
--PRINT @TableNameLike

DECLARE @curBuildQuery CURSOR
DECLARE @MainTablename varchar(255)

DECLARE @sMainTableQuery nVARCHAR (MAX)
SET @sMainTableQuery=N' SET @curBuildQuery = CURSOR LOCAL READ_ONLY FOR '
SET @sMainTableQuery=@sMainTableQuery + ' SELECT [name] FROM SYS.TABLES WHERE [name] like '+@TableNameLike
SET @sMainTableQuery=@sMainTableQuery + ' AND [name] NOT like ''zz%'''
SET @sMainTableQuery=@sMainTableQuery + ' ORDER BY [name]'
SET @sMainTableQuery=@sMainTableQuery + ' ;OPEN @curBuildQuery'
--PRINT @sMainTableQuery;return
--DELETE FROM zzResults_main

EXEC sp_executesql
@sMainTableQuery,
N'@curBuildQuery cursor OUTPUT', @curBuildQuery OUTPUT

FETCH NEXT FROM @curBuildQuery INTO @MainTablename
--PRINT @MainTablename
WHILE @@FETCH_STATUS = 0
BEGIN
-- PRINT @MainTablename
-- DECLARE @SearchString AS VARCHAR(255)
-- SET @SearchCountryName='aa'
SET @SearchString=ISNULL(@SearchString,'')
SET @SearchString=REPLACE(@SearchString,'''','''''')
SET @sMainTableQuery ='INSERT INTO zzResults_main'
SET @sMainTableQuery =@sMainTableQuery + ' ('
SET @sMainTableQuery =@sMainTableQuery + '[Import_Table],[ERS_UID],[File_Name],[Run_Number],[File_seqno],[Seqno],[Job_code],[Council_Name],[S_code] '
SET @sMainTableQuery =@sMainTableQuery + ',[EA_code 1],[EA_code 2],[EA_code 3]'
SET @sMainTableQuery =@sMainTableQuery + ',[PD_code],[poll_no],[barcode],[name],[on behalf of],[add1],[add2],[add3],[add4],[add5],[postcode]'
SET @sMainTableQuery =@sMainTableQuery + ',[UIM 1],[UIM 2],[UIM 3],[EA_text 1],[EA_text 2],[EA_text 3],[bpno 1],[bpno 2],[bpno 3]'
SET @sMainTableQuery =@sMainTableQuery + ',[bpno_text],[papercount],[Election1_link],[Election2_link],[Election3_link]'
SET @sMainTableQuery =@sMainTableQuery + ',[BRE_Address1],[BRE_Address2],[BRE_Address3],[BRE_Address4],[BRE_Address5],[BRE_Address6],[BRE_Postcode],[BRE_Licence]'
SET @sMainTableQuery =@sMainTableQuery + ',[polling_day],[waiver],[Overseas_Address],[Anonymous],[Proxy],[Restricted_Franchise] '
SET @sMainTableQuery =@sMainTableQuery + ',Main_Table'
SET @sMainTableQuery =@sMainTableQuery + ')'
SET @sMainTableQuery =@sMainTableQuery + ' SELECT '
SET @sMainTableQuery =@sMainTableQuery + '[Import_Table],[ERS_UID],[File_Name],[Run_Number],[File_seqno],[Seqno],[Job_code],[Council_Name],[S_code] '
SET @sMainTableQuery =@sMainTableQuery + ',[EA_code 1],[EA_code 2],[EA_code 3]'
SET @sMainTableQuery =@sMainTableQuery + ',[PD_code],[poll_no],[barcode],[name],[on behalf of],[add1],[add2],[add3],[add4],[add5],[postcode]'
SET @sMainTableQuery =@sMainTableQuery + ',[UIM 1],[UIM 2],[UIM 3],[EA_text 1],[EA_text 2],[EA_text 3],[bpno 1],[bpno 2],[bpno 3]'
SET @sMainTableQuery =@sMainTableQuery + ',[bpno_text],[papercount],[Election1_link],[Election2_link],[Election3_link]'
SET @sMainTableQuery =@sMainTableQuery + ',[BRE_Address1],[BRE_Address2],[BRE_Address3],[BRE_Address4],[BRE_Address5],[BRE_Address6],[BRE_Postcode],[BRE_Licence]'
SET @sMainTableQuery =@sMainTableQuery + ',[polling_day],[waiver],[Overseas_Address],[Anonymous],[Proxy],[Restricted_Franchise] '
SET @sMainTableQuery =@sMainTableQuery + ',''' + @MainTablename + ''''
SET @sMainTableQuery =@sMainTableQuery + ' FROM ' + @MainTablename
SET @sMainTableQuery=@sMainTableQuery+ ' WHERE '

if @Column1 is not null
BEGIN
SET @sMainTableQuery=@sMainTableQuery+ ' CHARINDEX('''+ @SearchString +''','+@Column1+')>0 '
SET @sMainTableQuery=@sMainTableQuery+ ' OR CHARINDEX('''+ REPLACE(@SearchString,' ','') +''','+@Column1+')>0 '
END


if @Column2 is not null
BEGIN
SET @sMainTableQuery=@sMainTableQuery+ ' OR CHARINDEX('''+ @SearchString +''','+@Column2+')>0 '
SET @sMainTableQuery=@sMainTableQuery+ ' OR CHARINDEX('''+ REPLACE(@SearchString,' ','') +''','+@Column2+')>0 '
END

if @Column3 is not null
BEGIN
SET @sMainTableQuery=@sMainTableQuery+ ' OR CHARINDEX('''+ @SearchString +''','+@Column3+')>0 '
SET @sMainTableQuery=@sMainTableQuery+ ' OR CHARINDEX('''+ REPLACE(@SearchString,' ','') +''','+@Column3+')>0 '
END

if @Column4 is not null
BEGIN
SET @sMainTableQuery=@sMainTableQuery+ ' OR CHARINDEX('''+ @SearchString +''','+@Column4+')>0 '
SET @sMainTableQuery=@sMainTableQuery+ ' OR CHARINDEX('''+ REPLACE(@SearchString,' ','') +''','+@Column4+')>0 '
END

if @Column5 is not null
BEGIN
SET @sMainTableQuery=@sMainTableQuery+ ' OR CHARINDEX('''+ @SearchString +''','+@Column5+')>0 '
SET @sMainTableQuery=@sMainTableQuery+ ' OR CHARINDEX('''+ REPLACE(@SearchString,' ','') +''','+@Column5+')>0 '
END

if @Column6 is not null
BEGIN
SET @sMainTableQuery=@sMainTableQuery+ ' OR CHARINDEX('''+ @SearchString +''','+@Column6+')>0 '
SET @sMainTableQuery=@sMainTableQuery+ ' OR CHARINDEX('''+ REPLACE(@SearchString,' ','') +''','+@Column6+')>0 '
END

if @Column7 is not null
BEGIN
SET @sMainTableQuery=@sMainTableQuery+ ' OR CHARINDEX('''+ @SearchString +''','+@Column7+')>0 '
SET @sMainTableQuery=@sMainTableQuery+ ' OR CHARINDEX('''+ REPLACE(@SearchString,' ','') +''','+@Column7+')>0 '
END

if @Column8 is not null
BEGIN
SET @sMainTableQuery=@sMainTableQuery+ ' OR CHARINDEX('''+ @SearchString +''','+@Column8+')>0 '
SET @sMainTableQuery=@sMainTableQuery+ ' OR CHARINDEX('''+ REPLACE(@SearchString,' ','') +''','+@Column8+')>0 '
END

if @Column9 is not null
BEGIN
SET @sMainTableQuery=@sMainTableQuery+ ' OR CHARINDEX('''+ @SearchString +''','+@Column9+')>0 '
SET @sMainTableQuery=@sMainTableQuery+ ' OR CHARINDEX('''+ REPLACE(@SearchString,' ','') +''','+@Column9+')>0 '
END

--PRINT @MainTablename
PRINT @sMainTableQuery
EXEC sp_executesql @sMainTableQuery
FETCH NEXT FROM @curBuildQuery INTO @MainTablename
END

CLOSE @curBuildQuery
DEALLOCATE @curBuildQuery

--Drop table zzResults_main
--select * into zzResults_main from R350_main where 1<>1
--ALTER TABLE zzResults_main ADD main_table VARCHAR(255)


--/*** use temp table 'zzResults_main_temp'
--select * from zzResults_main
--delete from zzResults_main_temp
--select * from zzResults_main_temp
--select * into zzResults_main_temp from zzResults_main where 1<>1
--insert into zzResults_main_temp select * from zzResults_main

--Delete from zzResults_main
--zz_spSearchForValueInColumns_And_TablesLike 'BFPO 715','_main','add1','add2','add3','add4','add5','postcode'


SELECT * FROM zzResults_main
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON