Wednesday 23 October 2019

Cursor : Loop Tables

Cursor to loop tables.

I use this pattern a lot in DBA  scripts...

DECLARE @SQL NVARCHAR(1000)
DECLARE @input_schema VARCHAR(100)
DECLARE @input_table VARCHAR(100)
DECLARE @current_schema VARCHAR(100)
DECLARE @current_table VARCHAR(100)

SET @input_schema = null
SET @input_table = null

DECLARE tablecursor CURSOR FOR
  SELECT table_schema, table_name
  FROM   information_schema.TABLES
  WHERE  TABLE_TYPE = 'BASE TABLE'
    AND  (TABLE_SCHEMA = @input_schema OR (@input_schema = '' OR @input_schema is null))
    AND  (TABLE_NAME = @input_table OR (@input_table = '' OR @input_table is null))

OPEN tablecursor

FETCH NEXT FROM tablecursor INTO @current_schema, @current_table

WHILE @@FETCH_STATUS = 0
  BEGIN
      SET @SQL = 'EXEC add_to_table_queue '+ @current_schema + ' , ' + @current_table

      PRINT @SQL
      --EXEC Sp_executesql @SQL

      FETCH NEXT FROM tablecursor INTO @current_schema, @current_table
  END

CLOSE tablecursor
DEALLOCATE tablecursor