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

Question

I have a collection (100+) of tables all of which contain two fields

RowChanged bit
ChangedFields bit

Now, an error has occurred which leave some entries with RowChanged = 1 while ChangedFields is empty. I therefore need to go through these and set RowChanged = 0 where ChangedFields empty.

I have achieved this by the following cursor.


BEGIN TRANSACTION
USE DatabaseName --Database name to clean

DECLARE @Table_Name VARCHAR(50)
DECLARE @Query VARCHAR(250)
DECLARE Table_Cursor CURSOR FOR SELECT Name FROM sys.tables;
DECLARE @Affected_Rows INTEGER = 0

OPEN Table_Cursor
FETCH NEXT FROM Table_Cursor INTO @Table_Name
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @Query = 'Update '+@Table_Name+' Set RowChanged = 0 Where RowChanged = 1 And (LEN(RTRIM(CONVERT(NVARCHAR(100), ChangedFields))) = 0 OR ChangedFields IS NULL)'
    EXEC (@Query)
    SET @Affected_Rows = @Affected_Rows + COALESCE(@@ROWCOUNT, 0)
    FETCH NEXT FROM Table_Cursor INTO @Table_Name
END
SELECT @Affected_Rows AS Affected_Rows

CLOSE  Table_Cursor
DEALLOCATE Table_Cursor

ROLLBACK --Change to COMMIT in order to save changes

While this does work, I have a genetic aversion against using cursors. Also I have just learned that Apply can in many cases achieve what Cursors did pre-2005.

What I need to do is to go though all tables in the database and check for the condition where RowChanged = 1 and ChangedFields like '' or NULL.

I have tried working this out with TVFs and what not, but I keep coming up short. While I could do operations in a single table, getting the list from sys.tables and doing something on several tables have eluded me.

score 2 · Answer 1 · answered Jul 31 '13 at 10:40

You can replace the cursor with while loop statement. Try using statement like the following to achive the desired o/p.

select name into #table from sys.tables
while (select count(*) from #table)>0
begin
    set rowcount 1  
    select @Table_Name = name from sys.tables
    set rowcount 0  
    .......
    .......

    delete from #table where name = @Table_Name
end

The above snippet is from sybase, you might need to make minor modifications for the syntax. I hope that helps.

score 1 · Answer 2 · edited May 23 '17 at 12:05

1

declare @stmt nvarchar(max)

select
   @stmt =
      isnull(@stmt + nchar(13) + nchar(10), '') + 
      'update '+ name +' set RowChanged = 0 Where RowChanged = 1 And (LEN(RTRIM(CONVERT(NVARCHAR(100), ChangedFields))) = 0 OR ChangedFields IS NULL)'
from sys.tables

print @stmt

sp_executesql @stmt = @stmt

there's also the undocumented stored procedure sp_MSforeachtable - SQL Server sp_msforeachtable usage to select only those tables which meet some condition. Actually, I've never used it in my work, so it's just for information.

edited May 23 '17 at 12:05

Community

1
1

answered Jul 31 '13 at 10:42

Roman Pekar

107,110
28
195
197

I've been warned not to use `sp_MSforeachtable`, it seems that you can't trust it when you have many tables in database. – Luis LL Jul 31 '13 at 10:54
Changed the answer - I've never used it in my work but heard about it, so I thought it would be nice to mention it. I like dynamic SQL more because imho it's more simple to maintain and I always can print statement to see what I will execute – Roman Pekar Jul 31 '13 at 10:56

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

2 Answers2