Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

2 Answers 2

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.

share|improve this answer

In order to prevent errors in your script you really need to check and verify

  1. That the user has access to the database
  2. That the table exists
  3. That the user has select rights on the table

Once you verified the above you can then execute the script to get the row count.

The script below should do the above for you. Its a bit crude and could be improved.

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

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

WHILE (@@FETCH_STATUS = 0)
  BEGIN           
    IF EXISTS(
                SELECT 
                    Name 
                FROM 
                    sys.sysdatabases 
                WHERE
                    @NomeBase in ( SELECT NAME from sys.sysdatabases where has_dbaccess(name) = 1)
                )
    BEGIN

        set @base =rtrim(@NomeBase) + '.dbo.sia_pais' -- database.dbo.table format
        IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(@base) AND type in (N'U'))
        BEGIN

            IF ( Select HAS_PERMS_BY_NAME(@base,'OBJECT', 'INSERT')) = 1
          BEGIN
            select @base
            set @select = 'SELECT cast(  count(*) as char(70)) from ' + @base 
            exec (@select)
          END
          ELSE
          BEGIN
              SELECT 'Table ' +  @base + 'No Select Access'
          END
        END
        ELSE
        BEGIN
            SELECT 'Table ' +  @base + 'Does Not Exist'
        END
    END
    ELSE
    BEGIN
        SELECT 'No Permission for Database: ' + @NomeBase
    END

    FETCH NEXT FROM CBases INTO  @NomeBase
  END
CLOSE CBases
DEALLOCATE CBase
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.