This is the cursor specifically where I picked up the problem
DECLARE @object_id INT
DECLARE @table_name VARCHAR(128)
DECLARE @col_name VARCHAR(128)
DECLARE @exec_sql_string VARCHAR(500)
DECLARE @exec_sql NVARCHAR(500)
DECLARE @max_id NVARCHAR(4000)
DECLARE @OutputParameter NVARCHAR(4000)
DECLARE @id_count NUMERIC(38, 0)
DECLARE @id_count_to NUMERIC(38, 0)
SET NOCOUNT ON
DECLARE recs CURSOR DYNAMIC
FOR
SELECT object_id
,name
FROM sys.tables ts
WHERE object_id IN (
SELECT object_id
FROM sys.all_columns
WHERE name = 'INDEX0'
)
ORDER BY name
OPEN recs
FETCH
FROM recs
INTO @object_id
,@table_name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @exec_sql_string = ''
DECLARE recs1 CURSOR DYNAMIC
FOR
SELECT name
FROM sys.all_columns
WHERE object_id = @object_id
AND name != 'ID'
AND name != 'INDEX0'
AND (
name NOT LIKE 'MAS%'
AND name NOT LIKE 'Z%'
)
OPEN recs1
FETCH
FROM recs1
INTO @col_name
WHILE @@FETCH_STATUS = 0
BEGIN
IF @exec_sql_string = ''
BEGIN
SET @exec_sql_string = 'DELETE FROM ' + @table_name + ' WHERE ' + @col_name + ' IS NULL '
END
ELSE
BEGIN
SET @exec_sql_string = @exec_sql_string + 'AND ' + @col_name + ' IS NULL '
END
FETCH
FROM recs1
INTO @col_name
END
SET @exec_sql = 'SELECT @OutputParameter = MAX(ID) FROM ' + @table_name
EXEC @max_id = sp_executesql @exec_sql
,N'@OutputParameter nvarchar(4000) output'
,@OutputParameter OUTPUT
IF LTRIM(RTRIM(@OutputParameter)) = ''
OR @OutputParameter IS NULL
BEGIN
SET @OutputParameter = '0'
END
SET @id_count = 1
IF @OutputParameter <> '0'
BEGIN
WHILE @id_count < @OutputParameter
BEGIN
SET @id_count_to = @id_count + 1000000
EXEC ('BEGIN TRANSACTION ' + @exec_sql_string + ' AND ID >= ' + @id_count + ' AND ID <= ' + @id_count_to + ' COMMIT TRANSACTION')
PRINT('FROM: ' + CAST(@id_count as varchar(150)) + ' TO: ' + CAST(@id_count as varchar(150)))
SET @id_count = @id_count + 1000000
END
SET @id_count_to = @id_count + 1000000
EXEC ('BEGIN TRANSACTION ' + @exec_sql_string + ' AND ID >= ' + @id_count + ' AND ID <= ' + @id_count_to + ' COMMIT TRANSACTION')
PRINT('FROM: ' + CAST(@id_count as varchar(150)) + ' TO: ' + CAST(@id_count as varchar(150)))
END
CLOSE recs1
DEALLOCATE recs1
FETCH
FROM recs
INTO @object_id
,@table_name
END
CLOSE recs
DEALLOCATE recs
GO