All Forums Analytics
JustMe 76 posts Joined 03/07
02 Feb 2011
Row counts for every table in a given database

Is there a macro or stored procedure anywhere that gives row counts for every table in a given database?

Dixxie 58 posts Joined 12/10
02 Feb 2011

Hi JustMe,

Replace MY_DB with your database name and Run this:

SELECT ' SELECT '' ' || T.tablename || ' '' as TableName , COUNT(*) as RowsNum FROM ' || TRIM(BOTH FROM databasename) || '.' || TRIM(BOTH FROM tablename) || ' UNION ' as X
FROM dbc.tables T
WHERE T.TableKind ='T' and T.databasename = 'MY_DB'
ORDER BY T. tablename

Copy the result to a new query, Delete the X in the first row and the UNION in the last row, and then execute the query.

Regards.

JustMe 76 posts Joined 03/07
03 Feb 2011

Thanks. I had created a dynamic statement like this, but was looking for a 'one-step' process. I appreciate your help!

dnoeth 4628 posts Joined 11/04
05 Feb 2011

You can extract the row counts from the statistics collected on those tables.
If stats are up to date, this needs no additional overhead.

Dieter

Dieter

satishkumar206 3 posts Joined 10/11
12 Oct 2011

hi

i need  2 insert that count in another table

Adeel Chaudhry 773 posts Joined 04/08
13 Oct 2011

satishkumar206 .... please post new questions in new threads!

-- If you are stuck at something .... consider it an opportunity to think anew.

chillerm 15 posts Joined 04/11
09 Jan 2013

I find this solution to be slightly more elegant.  You don't need to worry about truncation of table / db names.  It also allows you to get info for more than one database at time , given you are proving backups or something of that nature, which is what I did when I came up with this.
 
What do you think?
 
 

SELECT

T1.QRY || CASE WHEN chk <> 1 THEN ' union all' ELSE ';' END

FROM

(

SELECT

'select cast(' || '''' || TRIM(databasename) || '''' || ' as varchar(1000)) ' ||  ',' || 'cast(' || '''' || TRIM(tablename) || '''' || ' as varchar(1000))' || ', CAST(COUNT(*) AS DECIMAL(32,0)) FROM ' || TRIM(databasename) || '.' || TRIM(tablename)  AS QRY

,  SUM(1) OVER(ORDER BY qry ROWS UNBOUNDED PRECEDING) AS chk

FROM dbc.tables

WHERE

databasename IN ('<db_list>')

AND tablekind = 'T'

) T1

ORDER BY chk DESC

;

 

 

10 Jan 2013

Dieter
If Stats are up to date, how can I get the count?

dnoeth 4628 posts Joined 11/04
11 Jan 2013

There's a column NumRows in my StatsInfo view, which holds the rowcount. You just need to pick the row with the latest CollectTimestamp for each table using ROW_NUMBER:
http://developer.teradata.com/node/9598
Dieter

Dieter

reddy27 4 posts Joined 05/13
06 May 2013

hi

You must sign in to leave a comment.