CODEWITHSUNDEEP
t-sqlcursorfetch
bzamfir
bzamfir

Reputation: 4896

@@FETCH_STATUS returns -1 when trying to use cursor based on query on INFORMATION_SCHEMA

I try to create a SP that do some processing on every table that contains a specific field name

So my plan was to create a cursor to get all those tables, then prepare a dynamic sql for processing.

I have the following code

declare schema_cursor cursor
for
SELECT     INFORMATION_SCHEMA.TABLES.TABLE_SCHEMA, INFORMATION_SCHEMA.TABLES.TABLE_NAME
FROM         INFORMATION_SCHEMA.TABLES INNER JOIN
                  INFORMATION_SCHEMA.COLUMNS ON INFORMATION_SCHEMA.TABLES.TABLE_SCHEMA = INFORMATION_SCHEMA.COLUMNS.TABLE_SCHEMA AND 
                  INFORMATION_SCHEMA.TABLES.TABLE_NAME = INFORMATION_SCHEMA.COLUMNS.TABLE_NAME
WHERE     (INFORMATION_SCHEMA.TABLES.TABLE_TYPE = 'BASE TABLE') AND (INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME = N'TargetFieldName')

open schema_cursor
fetch next from schema_cursor into @schema, @table
while (@@FETCH_STATUS=0)
begin

If I run the query for cursor directly I get all table names I need But when I run code above, @@FETCH_STATUS returns -1 and no row is retrieved.

What am I doing wrong?

Thanks

Upvotes: 0

Views: 1405

Answers (2)

bzamfir
bzamfir

Reputation: 4896

Actually I found the problem. The code I posted was actually a simplified version of my actual code. My real select used by the cursor selected three fields as below, but fetch attempted to fetch only two fields.

SELECT 
     INFORMATION_SCHEMA.TABLES.TABLE_SCHEMA,
     INFORMATION_SCHEMA.TABLES.TABLE_NAME,
     INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME
-- ....

When I post the question, I thought to simplify it, but that actually fixed the issue :)

That's why my statement worked for Martin, but didn't for me - because by "mistake" the code I posted was correct, unlike the code I actually used.

Thanks, Martin, for taking time to post your answer which finally led me to discover the problem.

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 453047

This works fine for me. I doubt your assertion that the SELECT does return rows. Are you running the cursor code under a different login that doesn't have permissions to view the metadata perhaps?

SET NOCOUNT ON

DECLARE @schema SYSNAME,
        @table  SYSNAME
DECLARE schema_cursor CURSOR FOR
  SELECT INFORMATION_SCHEMA.TABLES.TABLE_SCHEMA,
         INFORMATION_SCHEMA.TABLES.TABLE_NAME
  FROM   INFORMATION_SCHEMA.TABLES
         INNER JOIN INFORMATION_SCHEMA.COLUMNS
           ON INFORMATION_SCHEMA.TABLES.TABLE_SCHEMA =
              INFORMATION_SCHEMA.COLUMNS.TABLE_SCHEMA
              AND INFORMATION_SCHEMA.TABLES.TABLE_NAME =
                  INFORMATION_SCHEMA.COLUMNS.TABLE_NAME
  WHERE  ( INFORMATION_SCHEMA.TABLES.TABLE_TYPE = 'BASE TABLE' ) 
  --AND (INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME = N'TargetFieldName')
OPEN schema_cursor

FETCH NEXT FROM schema_cursor INTO @schema, @table

WHILE ( @@FETCH_STATUS = 0 )
  BEGIN
      RAISERROR('%s %s', 0, 1, @schema, @table)

      FETCH NEXT FROM schema_cursor INTO @schema, @table
  END

CLOSE schema_cursor

DEALLOCATE schema_cursor

Upvotes: 1

Related Questions