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.
