Select all Table/View Names with each table Row Count in Teredata

Question

I have been stuck into a question. The question is I want to get all Table name with their Row Count from Teradata.

I have this query which gives me all View Name from a specific Schema.

I ] SELECT TableName FROM dbc.tables WHERE tablekind='V' AND databasename='SCHEMA' order by TableName;

& I have this query which gives me row count for a specific Table/View in Schema.

II ] SELECT COUNT(*) as RowsNum FROM SCHEMA.TABLE_NAME;

Now can anyone tell me what to do to get the result from Query I (TableName) and put it into QUERY II (TABLE_NAME) You help will be appreciated.

Thanks in advance,

Vrinda

Answer 1

This is a SP to collect row counts from all tables within a database, it's very basic, no error checking etc.

It shows a cursor and dynamic SQL using dbc.SysExecSQL or EXECUTE IMMEDIATE:

CREATE SET TABLE RowCounts
     (
      DatabaseName VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
      TableName VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
      RowCount BIGINT,
      COllectTimeStamp TIMESTAMP(2))
PRIMARY INDEX ( DatabaseName ,TableName )
;

REPLACE PROCEDURE GetRowCounts(IN DBName VARCHAR(30))
BEGIN
  DECLARE SqlTxt VARCHAR(500);
   FOR cur AS
      SELECT
         TRIM(DatabaseName) AS DBName,
         TRIM(TableName) AS TabName
      FROM dbc.Tables
      WHERE DatabaseName = :DBName
      AND TableKind = 'T'
   DO
     SET SqlTxt =
         'INSERT INTO RowCounts ' ||
         'SELECT ' ||
         '''' || cur.DBName || '''' || ',' ||
         '''' || cur.TabName || '''' || ',' ||
         'CAST(COUNT(*) AS BIGINT)' ||  ',' ||
         'CURRENT_TIMESTAMP(2) ' ||
         'FROM ' || cur.DBName ||
         '.' || cur.TabName || ';';

     --CALL dbc.sysexecsql(:SqlTxt);
     EXECUTE IMMEDIATE sqlTxt;
   END FOR;
END;

If you can't create a table or SP you might use a VOLATILE TABLE (as DrBailey suggested) and run the INSERTs returned by following query:

SELECT
   'INSERT INTO RowCounts ' ||
   'SELECT ' ||
   '''' || DatabaseName || '''' || ',' ||
   '''' || TableName || '''' || ',' ||
   'CAST(COUNT(*) AS BIGINT)' ||  ',' ||
   'CURRENT_TIMESTAMP(2) ' ||
   'FROM ' || DatabaseName ||
   '.' || TableName || ';'
FROM dbc.tablesV 
WHERE tablekind='V' 
AND databasename='schema' 
ORDER BY TableName;

But a routine like this might already exist on your system, you might ask you DBA. If it dosn't have to be 100% accurate this info might also be extracted from collected statistics.

Answer 2

Use dnoeth's answer but instead use create "create volatile table" this will use your spool to create the table and will delete all data when your session is closed. You need no write access to use volatile tables.

Answer 3

Is there any way to find the row count of a table/view from system tables with out using count(). Count() took ages to run for big tables. dbc.statsv view only include tables as we are not able to collect the stat of a view.