Search for Data - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Development (2000)

Search for Data

Author

Topic

vgr.raju
Yak Posting Veteran

95 Posts

Posted - 2007-01-04 : 18:11:22

Hi ALL,
I have an interesting requirement.
I have to search for particular piece of Data in all the tables and replace with new value.

I thought of using the cursors which iterates through table and columns and so on.

That takes lot of time as our DB is about 80 Gig.Does any one come across these kind of situation and your suggestions and solutions are really appreciated..

Thanks!
Raju
http://rajusqlblog.blogspot.com/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-04 : 19:30:23

UPDATE <YourTableNameHere>
SET <YourValueColumnNameHere> = <YourNewValueHere>
WHERE <YourCriteriaColumnNameHere> = <SomeValueToFindHere>

Peter Larsson
Helsingborg, Sweden

vgr.raju
Yak Posting Veteran

95 Posts

Posted - 2007-01-04 : 19:45:51

Hi,
Thanks for your reply. My requirement is ,I do not know what are the tables and what are the columns the data lives.
I want to lookup the data then make the changes. I have written lookup using cursors.

declare @table_name nvarchar(255)
declare @column_name nvarchar(255)
declare @database nvarchar(255)
declare @searchkey nvarchar(2550)
declare @sql nvarchar(4000)
set @database= db_name()
set @searchkey= 'SomeData'

declare c cursor for
select table_name,column_name from information_schema.columns
where table_catalog=@database and table_name not like 'syncobj%'
open c
fetch next from c into @table_name,@column_name
while @@fetch_status=0
begin
set @sql='select * from '+ @table_name+' where ' + @column_name+' like ''%'+ @searchkey +'%'''
print @sql
exec sp_executesql @sql
fetch next from c into @table_name,@column_name
end
close c
deallocate c

That seems to be very inefficient ..(with forward only/read only hint)..Is there any other better approach??
Your help would be much appreciated..

Thanks!
Raju
http://rajusqlblog.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-01-05 : 08:24:42

http://www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources