SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 How Search A Value In A DataBase.
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Vipps
Starting Member

India
4 Posts

Posted - 09/06/2006 :  08:34:34  Show Profile  Send Vipps a Yahoo! Message  Reply with Quote
--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

India
22755 Posts

Posted - 09/06/2006 :  09:01:29  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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

Edited by - madhivanan on 09/06/2006 09:03:31
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000