Count rows from N tables

Database
Highlighted

Count rows from N tables

Hi.

 

I'm hoping one or some of you may have an answer for me.  I'm trying to produce a list of row counts for all tables in a single database, where the number of tables may or may not be known.  Tables may potentially be grouped according to a prefix along the lines of "XXX_" or "XXXX_".  These tables contain hundreds of millions of rows between them, and my intention is to verify the combined row count for all tables matching such a prefix.  Is there a way to do this (in Teradata Studio v16) without hard-coding in the specific table names?  Thanks.

 

PS: I've seen many posts here and there with queries which don't achieve that goal, but which produce massive lists of select queries which might achieve that gorl for some people in rare cases.  It won't achieve my goal.  It'll just blow out the spool space.


Accepted Solutions
Teradata Employee

Re: Count rows from N tables

Hi Adam_Webber,

 

The basic solution is to build the select count(*) from a select of dbc.TablesV :

select 'select "' || DatabaseName || '" as DatabaseName, "'
    || TableName || '" as TableName, count(*) from "'
    || DatabaseName || '"."' || TableName || '";' (title '')
  from dbc.TablesV
 where TableKind   in ('O', 'T')
   and DatabaseName = 'SomeDatabase';

Then you can run this script where you want.

With BTEQ you can automatize the generation and the execution with two EXPORT commands.

 

If you want something quicker, you can recollect summary stats on your tables (that's quite fast) then look at the RowCount column in dbc.StatsV for StatsId = 0.

 

1 ACCEPTED SOLUTION
6 REPLIES 6
Teradata Employee

Re: Count rows from N tables

Hi Adam_Webber,

 

The basic solution is to build the select count(*) from a select of dbc.TablesV :

select 'select "' || DatabaseName || '" as DatabaseName, "'
    || TableName || '" as TableName, count(*) from "'
    || DatabaseName || '"."' || TableName || '";' (title '')
  from dbc.TablesV
 where TableKind   in ('O', 'T')
   and DatabaseName = 'SomeDatabase';

Then you can run this script where you want.

With BTEQ you can automatize the generation and the execution with two EXPORT commands.

 

If you want something quicker, you can recollect summary stats on your tables (that's quite fast) then look at the RowCount column in dbc.StatsV for StatsId = 0.

 

Teradata Employee

Re: Count rows from N tables

Hi Adam,

 

If you are using Teradata Studio this may help you.

Row count menu action shows the no of rows on each table when a set of tables selected from Object List Viewer.Please refer the attached image.

RowCountOfMultipleTables.png

Re: Count rows from N tables

Hi Waldar.

 

Thanks.  I've tried that method, which is just a shortcut for writing a list of select queries.  However, the resulting list blows out the spool space if I try to do them all at once, and if I do them one at a time I'm back to square one.  Running a large number of select queries is only viable for a small database.

Teradata Employee

Re: Count rows from N tables

Waldar's second suggestion, using statsv is absolutely the fastest way.

 

It does rely on you having collected summary stats on every table but you should be doing that as a best practice anyways.

 

locking row for access
select databasename, tablename, rowcount (decimal(18))
from dbc.statsV
where columnname is null
order by 1,2
;

 

dave

Re: Count rows from N tables


@Waldar wrote:

 

If you want something quicker, you can recollect summary stats on your tables (that's quite fast) then look at the RowCount column in dbc.StatsV for StatsId = 0.

 


Hi again Waldar.  That was indeed the path to glory.  Thanks.  Smiley Happy

Re: Count rows from N tables

Thanks @srilatha but unfortunately that function is not available for us.