sql - Replacing a cursor with apply to loop through all tables in a database -

i have collection (100+) of tables of contain 2 fields

rowchanged bit changedfields bit 

now, error has occurred leave entries rowchanged = 1 while changedfields empty. therefore need go through these , set rowchanged = 0 changedfields empty.

i have achieved following cursor.

 begin transaction use databasename --database name clean  declare @table_name varchar(50) declare @query varchar(250) declare table_cursor cursor select name sys.tables; declare @affected_rows integer = 0  open table_cursor fetch next table_cursor @table_name while @@fetch_status = 0 begin     set @query = 'update '+@table_name+' set rowchanged = 0 rowchanged = 1 , (len(rtrim(convert(nvarchar(100), changedfields))) = 0 or changedfields null)'     exec (@query)     set @affected_rows = @affected_rows + coalesce(@@rowcount, 0)     fetch next table_cursor @table_name end select @affected_rows affected_rows  close  table_cursor deallocate table_cursor  rollback --change commit in order save changes  

while work, have genetic aversion against using cursors. have learned apply can in many cases achieve cursors did pre-2005.

what need go though tables in database , check condition rowchanged = 1 , changedfields '' or null.

i have tried working out tvfs , not, keep coming short. while operations in single table, getting list sys.tables , doing on several tables have eluded me.

you can replace cursor while loop statement. try using statement following achive desired o/p.

select name #table sys.tables while (select count(*) #table)>0 begin     set rowcount 1       select @table_name = name sys.tables     set rowcount 0       .......     .......      delete #table name = @table_name end 

the above snippet sybase, might need make minor modifications syntax. hope helps.