Wednesday, November 14, 2007

Search into database tables - Without Cursors

Is there such a thing as a task where you would not need a cursor? Hidden in the depths of the master database are a series of stored procedures that can replace some cursors with these one-liners. Behind the scenes, cursors are still used, but they will save you tons of development time.
Traditionally if you wanted to run a DBCC CHECKTABLE on every table in a database you'd have to write an elaborate cursor like below :

create table #Result
tablename nvarchar(1000),
columnname nvarchar(1000),
searchvalue nvarchar(1000)

declare @searchstring as varchar(1000)
declare @searchscript as varchar(2000)
set @searchstring = 'Hossam'
set @searchscript =
'declare @sql as varchar(8000)
set @sql = ''select '''''''' tablename,'''''''' columnname,'''''''' value where 1=0''
@sql = @sql + '' union all select ''''?'''','''''' +
name + '''''',['' + name + ''] from ? where ['' + name + ''] like ''''%' + @searchstring + '%''''''
xtype in (175,239,231,167)
and id=object_id(''?'')
insert into #Result
Exec (@sql)
print ''search is completed on ?.'''
exec sp_msforeachtable @searchscript
select * from #Result
--drop table #Result
Post a Comment