SQL Server – Convert SELECT COUNT(*) to HTML Table

There's lots of options.

• PowerShell
• SSMS's Multi Server Query Functionality
• Linked Servers
• Batch File with a loop and sqlcmd variables

I would use the system metadata to make all of this an easier experience. The query from this article will show you how to use the metadata to determine the rowcount for all your tables in a database.

It'd look like

SELECT
    s.[Name] as [Schema]
,   t.[name] as [Table]
,   SUM(p.rows) as [RowCount]
,   db_name() AS dbname
FROM
    sys.schemas s
    LEFT OUTER JOIN 
        sys.tables t
        ON s.schema_id = t.schema_id
    LEFT OUTER JOIN 
        sys.partitions p
        ON t.object_id = p.object_id
    LEFT OUTER JOIN  
        sys.allocation_units a
        ON p.partition_id = a.container_id
WHERE
    p.index_id  in(0,1) -- 0 heap table , 1 table with clustered index
    AND p.rows is not null
    AND a.type = 1  -- row-data only , not LOB
    and t.[name] = 'sia_pais'
GROUP BY 
    s.[Name]
,   t.[name]
ORDER BY 
    1
,   2;

Great, now you know the row count for a single table. You can then patch that into your existing cursor. The other nice thing about this route is it doesn't matter whether the table exists or not. It will simply not return a row if the table is gone.

And for the sake of providing a mostly full solution

DECLARE 
    @NomeBase as char(60)
,   @base as nvarchar(4000)
,   @select as nvarchar(4000);

-- Create a template query to capture table counts
-- Since it's lightning fast, I delay filtering until the end
SELECT
    @base = N'USE <dbname/>;
    INSERT INTO
        ##rowCounts
    SELECT
        s.[Name] as [Schema]
    ,   t.[name] as [Table]
    ,   SUM(p.rows) as [RowCount]
    ,   db_name() AS dbname
    FROM
        sys.schemas s
        LEFT OUTER JOIN 
            sys.tables t
            ON s.schema_id = t.schema_id
        LEFT OUTER JOIN 
            sys.partitions p
            ON t.object_id = p.object_id
        LEFT OUTER JOIN  
            sys.allocation_units a
            ON p.partition_id = a.container_id
    WHERE
        p.index_id  in(0,1) 
        AND p.rows is not null
        AND a.type = 1 
    GROUP BY 
        s.[Name]
    ,   t.[name]
    ';

-- 
if object_id('tempdb..##rowCounts') IS NOT NULL
BEGIN
    DROP TABLE ##rowCounts;
END;

-- Create a global temporary table to keep track of table counts
CREATE TABLE
    ##RowCounts
(
    [SchemaName] sysname
,   [TableName] sysname
,   [RowCounts] bigint 
,   [DatabaseName] sysname
);

-- Enumerate through all of your databases
DECLARE CBases CURSOR FOR  
-- select name from sys.databases
select databasename from sig_orgao_web
 --gets all my databases
OPEN CBases
FETCH NEXT FROM CBases INTO  @NomeBase 

WHILE (@@FETCH_STATUS = 0)
  BEGIN
    -- Replace our token with the actual database name
    SET @select = replace(@base, '<dbname/>', @NomeBase);

    EXEC(@select);
    FETCH NEXT FROM CBases INTO  @NomeBase;
  END;
CLOSE CBases;
DEALLOCATE CBases;

-- Retrieve actual counts
SELECT
*
FROM
    ##RowCounts RC
WHERE
    RC.TableName = 'sia_pais'
    AND RC.SchemaName = 'dbo';

What remains? Your account might not have access to a given database. The template could be further armored by wrapping a try/catch around the whole thing. But for this case, I assume you have access and if you don't, it comes roaring back at you instead of silently swallowing errors.