Need to declare a cursor with a variable in the declaration

I had a problem with a maintenance procedure and need to create a second one where I declare a cursor with a list of database ID and pass them into another cursor to get a list of tables for each database.

Current problem is that in the inner cursor even though it runs use [database_name], when i declare it and specify my query it selects the tables from the master database. it doesn’t change the database context before going into the inner cursor.

DECLARE @db varchar(128) 
declare @cmd varchar(1024)
declare @table varchar(255)
declare @cmd2 varchar(1024)

   DECLARE crDB CURSOR global FORWARD_only FOR
   SELECT [name] FROM sys.databases WHERE database_id > 4
   and database_id in (33) ORDER BY [name]

   OPEN crDB
   FETCH crDB INTO @db
   WHILE @@FETCH_STATUS = 0
   BEGIN 
   SET @cmd = 'USE [' + @db +']'
   EXEC (@cmd)


           DECLARE crTB CURSOR LOCAL FAST_FORWARD FOR
           select [name] from sys.objects where type = 'u' ORDER BY [name]
           OPEN crTB
           FETCH crTB INTO @table
           WHILE @@FETCH_STATUS = 0
           BEGIN
           SET @cmd2 = 'Update Statistics ' + @table + CHAR(13) 
           PRINT @cmd2
           EXEC (@cmd2)
           end
           CLOSE crTB
           DEALLOCATE crTB

    FETCH crDB INTO @db
    END
    CLOSE crDB
    DEALLOCATE crDB

GO

Answer

The issue with your inner cursor, is scope. You can to do 2 things here. You have to move your inner cursor to right after the USE [' + @db like:

DECLARE @db VARCHAR(128);
DECLARE @cmd VARCHAR(1024);
DECLARE @table VARCHAR(255);
DECLARE @cmd2 VARCHAR(1024);

DECLARE crDB CURSOR GLOBAL READ_ONLY FORWARD_ONLY FOR
SELECT name
FROM sys.databases
WHERE database_id > 4
  AND database_id IN (33)
ORDER BY name;

OPEN crDB;

FETCH crDB
INTO @db;

WHILE @@FETCH_STATUS = 0
  BEGIN
  SET @cmd = 'USE [' + @db + ']
    GO;
  DECLARE crTB CURSOR LOCAL FAST_FORWARD FOR
  SELECT name
  FROM sys.objects
  WHERE type = ''u'';
  ORDER BY name;

  OPEN crTB;

  FETCH NEXT FROM crTB
  INTO @table;

  WHILE @@FETCH_STATUS = 0
    BEGIN
    SET @cmd2 = ''Update Statistics '' + @table + CHAR(13);

    PRINT @cmd2;

    EXEC (@cmd2);
    END;    

  CLOSE crTB;
  DEALLOCATE crTB;
    ';

  EXEC (@cmd);

  FETCH NEXT FROM crDB
  INTO @db;
  END;

CLOSE crDB;
DEALLOCATE crDB;

Or you can get rid of the inner cursor altogether and use sys.sp_MSforeachtable:

WHILE @@FETCH_STATUS = 0
  BEGIN
  SET @cmd = 'USE [' + @db + ']
    GO;
    EXEC sys.sp_MSforeachtable @command1 = ''UPDATE STATISTICS ?;''';

  EXEC (@cmd);