Row counts for every table in a given database

Analytics
Enthusiast

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?
9 REPLIES
Enthusiast

Re: Row counts for every table in a given database

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.
Enthusiast

Re: Row counts for every table in a given database

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

Re: Row counts for every table in a given database

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

Re: Row counts for every table in a given database

hi

i need  2 insert that count in another table

Teradata Employee

Re: Row counts for every table in a given database

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

Enthusiast

Re: Row counts for every table in a given database

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

;

Enthusiast

Re: Row counts for every table in a given database

Dieter

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

Junior Contributor

Re: Row counts for every table in a given database

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

Re: Row counts for every table in a given database

hi