sqlteradatarowcounttablenamedynamic-queries

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


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


Solution

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