Row count from a certain table in multiple databases I need to know the row count of a certain table that exists (or don't) in multiple databases.

Here's what I came with:

DECLARE @NomeBase as char(60)
declare @base as char(100)
declare @select as char(1000)

DECLARE CBases CURSOR FOR  select databasename from sig_orgao_web
 --gets all my databases
OPEN CBases
FETCH NEXT FROM CBases INTO  @NomeBase 

WHILE (@@FETCH_STATUS = 0)
  BEGIN           
    set @base =rtrim(@NomeBase) + '.dbo.sia_pais' -- database.dbo.table format
    select @base
    set @select = 'SELECT cast(  count(*) as char(70)) from ' + @base 
    exec (@select)

    FETCH NEXT FROM CBases INTO  @NomeBase
  END
CLOSE CBases
DEALLOCATE CBases


I know that tablenames have to be static, so I'm creating the select statements dynamically. The results are like this:

SIIG_DAT_DESENV.dbo.sia_pais    
13
SIIG_DAT_HOMOLOGACAO.dbo.sia_pais
13


And it goes on.

The thing is, if the table doesn't exist in my database or if the user which is running this script on management studio doesn't have access to a certain database, an error will be thrown.

Is there a better way to do this?
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 &lt;dbname/&gt;;
    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, '&lt;dbname/&gt;', @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.