Stored Procedure for Searching any Value in a Table

This Stored Procedure is for Searching any value in any column in a table


Exec searchforvalues 'param1','param2'

param1 - Tablename
param2 - A String(including wildcard characters)


Use it for your Convienience!!!

---Coding---

alter procedure searchforvalues
@tablename nvarchar(100),@searchstring nvarchar(100)
as

declare @c_id nvarchar(1000)
declare @sstring nvarchar(1000)
declare @sql nvarchar(1000)
declare @String nvarchar(1000)

set nocount on

set @searchstring = ''''+@searchstring+''''

if exists(select * from information_schema.tables where table_name like +@tablename)
begin

if exists(select * from information_schema.tables where table_name like 'Sql_search')

begin
set @sql=''
set @sql=@sql+N'drop table Sql_search'
exec sp_executesql @sql
end


set @sql=''
set @sql=@sql+'create table Sql_search(column_name nvarchar(1000),searchstring nvarchar(1000))'
exec sp_executesql @sql


declare c1 cursor for
select column_name from information_schema.columns
where
table_name like @tablename

--Cursor Starts
open c1

fetch next from c1
into @c_id

while @@fetch_status = 0

begin
set @String =''''+@c_id+''''
set @sql=''
set @sql=@sql+'insert into sql_search select '+@string+',['+@c_id+'] from '+@tablename+' where ['+@c_id +'] like '+@searchstring
exec sp_executesql @sql
fetch next from c1
into @c_id
end

close c1
deallocate c1

--Cursor Ends

--Output
set @sql=''
set @sql=@sql+'select column_name,searchstring ,count(*) as [Count] from sql_search
group by column_name,searchstring
order by column_name'
exec sp_executesql @sql
print @sql



exec('drop table sql_search')

end

else

Print 'Table Does Not Exist'