Accumulate query in variable n times during a while

As indicated in the comment, you would have to build your query and store the result of each query in a consolidated.

USE master;
GO
DECLARE @HFM_DB VARCHAR(100)
   ,@Consulta VARCHAR(MAX)

DECLARE Cursor_DB CURSOR
FOR
SELECT name
FROM sys.databases
WHERE name LIKE '%HFM%';
OPEN Cursor_DB;
FETCH NEXT FROM Cursor_DB
INTO @HFM_DB;

SELECT @Consulta = '
DECLARE @Consolidado AS TABLE(
    [DB_name] VARCHAR(MAX),
    table_name VARCHAR(MAX),
    rows BIGINT
);'

WHILE (
          @@FETCH_STATUS = 0
      )
BEGIN

    SELECT @Consulta = @Consulta + 
         N'USE ' + @HFM_DB + N'; 
INSERT INTO @Consolidado
SELECT 
    ''' + @HFM_DB + N''',
    t.NAME, 
    i.rows 
FROM ' + @HFM_DB + '.sys.tables t 
INNER JOIN sysindexes i 
    ON (t.object_id = i.id AND i.indid < 2)
WHERE 
    t.name LIKE ''%_DATA_AUDIT'' 
    OR t.name LIKE ''%_TASK_AUDIT'' 
    OR t.name=''HFM_ERRORLOG'';';

    FETCH NEXT FROM Cursor_DB
    INTO @HFM_DB;

END;

SELECT @Consulta = @Consulta + '
SELECT DB_name, table_name, rows FROM @Consolidado;'

EXEC sp_sqlexec @Consulta;
--PRINT @Consulta;

CLOSE Cursor_DB;
DEALLOCATE Cursor_DB;

You should simply use SUM , and do not put the rows of each table:

USE master;
GO
DECLARE @HFM_DB varchar(100)
DECLARE @ROWCOUNT varchar (800)
DECLARE @BASE varchar (800)

DECLARE Cursor_DB CURSOR FOR SELECT name FROM sys.databases WHERE name like '%HFM%'
OPEN Cursor_DB
FETCH NEXT FROM Cursor_DB INTO  @HFM_DB 

WHILE (@@FETCH_STATUS= 0)
BEGIN
                SET @BASE=N'USE'
                --SELECT @BASE
                SELECT @ROWCOUNT= @BASE + ' ' +@HFM_DB + N' SELECT ''' +@HFM_DB + N''' as DB_name,
                               SUM(i.rows) rows
                FROM ' + @HFM_DB+ '.sys.tables t INNER JOIN sysindexes i ON (t.object_id = i.id AND i.indid < 2) 
WHERE t.name LIKE ''%_DATA_AUDIT'' OR t.name LIKE ''%_TASK_AUDIT'' OR t.name=''HFM_ERRORLOG'''
   FETCH NEXT FROM Cursor_DB INTO  @HFM_DB;
  -- SELECT @ROWCOUNT
  EXEC sp_sqlexec @ROWCOUNT
  END;

CLOSE Cursor_DB;
DEALLOCATE Cursor_DB;

enter the description of the image here Try to group your t-sql so that it looks like this:

  

SELECT t.NAME as [table_name], sum (i.rows) as Rows FROM sys.tables t     INNER JOIN sysindexes ON (t.object_id = i.id AND i.indid < 2)   WHERE t.name LIKE 'TMP%'   GROUP BY t.NAME   WITH ROLLUP

I hope I have helped

Remember to use GROUP BY and its ROLLUP clause

USE master;
GO
DECLARE @HFM_DB varchar(100)
DECLARE @ROWCOUNT varchar (800)
DECLARE @BASE varchar (800)

DECLARE Cursor_DB CURSOR FOR SELECT name FROM sys.databases WHERE name like '%HFM%'
OPEN Cursor_DB
FETCH NEXT FROM Cursor_DB INTO  @HFM_DB 

WHILE (@@FETCH_STATUS= 0)
BEGIN
                SET @BASE=N'USE'
                --SELECT @BASE
                SELECT @ROWCOUNT= @BASE + ' ' +@HFM_DB + N' SELECT ''' +@HFM_DB + N''' as DB_name,
                               t.NAME as [table_name], 
                               sum(i.rows) as ROWS 
                FROM ' + @HFM_DB+ '.sys.tables t INNER JOIN sysindexes i ON (t.object_id = i.id AND i.indid < 2) 
WHERE t.name LIKE ''%_DATA_AUDIT'' OR t.name LIKE ''%_TASK_AUDIT'' OR t.name=''HFM_ERRORLOG'' **GROUP BY ''' +@HFM_DB + N''', t.NAME WITH ROLLUP'**
   FETCH NEXT FROM Cursor_DB INTO  @HFM_DB;
  -- SELECT @ROWCOUNT
  EXEC sp_sqlexec @ROWCOUNT
  END;

CLOSE Cursor_DB;
DEALLOCATE Cursor_DB;

To begin, sp_sqlexec understand is "deprecated", the correct thing would be to use sp_executesql . Beyond that, the only alternative is to "insert" the records obtained in each dynamic query into a temporary table created outside the cycle.

For example:

CREATE TABLE #Resultados (
             db_name    VARCHAR(255),
             table_name VARCHAR(255),
             rows       INT

)

Then within the cycle, you add to your dynamic query:

INSERT INTO #Resultados (db_name, table_name, rows)

Finally when leaving the cycle

SELECT db_name, table_name, rows
       FROM Resultados