How Search A Value In A DataBase.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Script Library

How Search A Value In A DataBase.

Author

Topic

Vipps
Starting Member

4 Posts

Posted - 2006-09-06 : 08:34:34

--Author:=> Kapil Choudhary Jaipur (India)
--Motive:- Search A Text value In A DataBase With The Table Name.

create procedure [search_value]
@fstr nvarchar(128)
with encryption
as

set nocount on
declare @rc int --Counter Variable For The Cursor 1.
declare @rcc int --Counter Variable For The Cursor 2.
declare @tc nvarchar(128) --TABLE_CATALOG.
declare @ts nvarchar(128) --TABLE_SCHEMA.
declare @tn nvarchar(128) --TABLE_NAME.
declare @tt nvarchar(128) --TABLE_TYPE.
declare @Ctn nvarchar(128) --Column Table Name.
declare @Cts nvarchar(128) --Column Table Schema.
declare @Ccn nvarchar(128) --Column Column Name.
declare @Cdt nvarchar(128) --Column Data Type.
declare @currow1 int --Total Row Count For The Cursor 1.
declare @currow2 int --Total Row Count For The Cursor 2.
declare @str nvarchar(128) --Dynamically Created Query String.
declare @strresult nvarchar(128)--Value Stored From Temp Table.
declare @findwhat nvarchar(128) -- String To Be Searched.
set @rc=1
create table #temp (colval nvarchar(128))
create table #myresult(Table_Name nvarchar(128),Column_Name nvarchar(128),Column_Value nvarchar(128))
declare mycur1 cursor static for
select TABLE_CATALOG,TABLE_SCHEMA ,TABLE_NAME,TABLE_TYPE
from INFORMATION_SCHEMA.TABLES
where TABLE_TYPE='BASE TABLE'
open mycur1
set @currow1=@@cursor_rows
--print 'table rows = '+str(@currow1)
while @rc<=@currow1
begin
fetch next from mycur1 into @tc,@ts,@tn,@tt
declare mycolcur cursor static for
select TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,DATA_TYPE
from information_schema.columns
where TABLE_CATALOG=@tc and TABLE_SCHEMA=@ts and TABLE_NAME=@tn and data_type in('char','varchar','nchar','nvarchar')
open mycolcur
set @findwhat='wa'--Your Search Value Here
set @rcc=1
set @currow2=@@cursor_rows
--print 'table col = '+str(@currow2)
--print 'Table name'+' '+'owener'+' '+'Column Name'
--print '-----------------------------------------------'
while @rcc<=@currow2
begin
fetch next from mycolcur into @Cts,@Ctn,@Ccn,@Cdt
-- print @ctn+' '+@cts+' '+@ccn
set @rcc=@rcc+1
set @str='select '+quotename(@ccn) + ' from ' +quotename(@cts)+'.'+ quotename(@ctn) + ' where ' + quotename(@ccn) +' like '+char(39)+@findwhat+char(39)
set @str='insert into #temp(colval) '+@str
--print @str
exec (@str)
if exists(select * from #temp)
begin
select @strresult= colval from #temp
--print 'colval =========>>>>> ' + @strresult
delete from #temp
insert into #myresult(Table_Name,Column_Name,Column_Value) values(@ctn,@ccn,@strresult)
end
end
close mycolcur
deallocate mycolcur

set @rc=@rc+1
end
--print @@cursor_rows
select * from #myresult
close mycur1
deallocate mycur1
drop table #temp
drop table #myresult
set nocount off
Go

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-09-06 : 09:01:29

Use Code tags [ Code ] your script [/ Code ] (Remove the spaces) to make the code more readable

Also refer this
http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources