Hi All,

I have written a stored procedure to search for value existing in a database. It searches all char and varchar columns and returns tablename and columnname which has the search value.

My problem is : The output also returns Exec (@qry) values for all the searches , which is a junk. At the end it pulls out the expected result.

I want to avoid printing this output of exec(@qry) .

To get clear idea of what I am saying, you can run this stored procedure in Pubs database and see the output.

I will appreciate if someone can help me as soon as possible.

Thanks,
Varad

--exec SPU_Findstring white
/************************************************** **********************************************/
/* */
/* This Stored Procedure is used to search a value in char and Varchar columns in a database */
/* This returns the Tablename and Column Name where the passed string is available. */
/* If you run this (exec SPU_Findstring white) in the pubs database this has to return one */
/* record . Since authors table has a value "white" in au_lname column. */
/* Tablename ColumnName */
/* --------- ---------- */
/* authors au_lname */
/* */
/************************************************** **********************************************/


drop procedure SPU_Findstring
go
create procedure SPU_Findstring
@stringname as varchar(50)
as
SET NOCOUNT ON
SET ANSI_WARNINGS ON
SET ANSI_NULLS ON
Declare
@str_name varchar(50)
Begin
Select @str_name = @stringname

Create table #FoundString
(
fs_tname varchar(50),
fs_cname varchar(50)
)

Select
substring(a.name,1,50) ATC_tname ,
substring(b.name,1,50) ATC_cname
INTO #All_TablesColumns
from
sysobjects a
inner join
syscolumns b
on a.id = b.id and a.status > 0 and b.xtype in (167,175)
where a.type = 'U'


Declare @tname varchar(100)
Declare @cname varchar(100)
Declare @qry varchar(1000)
Declare @tid int

Declare All_TablesColumns cursor FOR
Select
ATC_tname , ATC_cname
from
#All_TablesColumns

OPEN All_TablesColumns

FETCH NEXT FROM All_TablesColumns INTO @tname, @cname

WHILE @@FETCH_STATUS = 0

BEGIN

Set @qry = 'select * from '+ @tname + ' where ' + @cname + '= ''' + @str_name + ''''
Exec (@qry)

If @@rowcount > 0
Begin
Insert into #FoundString values (@tname , @cname)
End

FETCH NEXT FROM All_TablesColumns INTO @tname, @cname
End

Select * from #FoundString

Drop table #All_TablesColumns
Drop table #FoundString

CLOSE All_TablesColumns
Deallocate All_TablesColumns
END