Home Page for the TeradataForum
 
https:

Archives of the TeradataForum

Message Posted: Tue, 26 Jul 2016 @ 13:13:44 GMT


     
  <Prev Next>   <<First <Prev Next> Last>>  


Subj:   Re: Determine the row count for all tables in a particular database
 
From:   VanWyk, Arnoldus A

TableStatsV and StatsV typically have multiple rows per table. I use the following query to pull the Row count from most recent statistics collection event having the highest sample rate in the hopes of getting the most accurate count.

-- Table Row counts from most recent statistics collection event having the highest sample rate

     SELECT DatabaseName
        ,TABLENAME
        ,RowCount
        ,LastCollectTimeStamp
     FROM (
        SELECT
           DatabaseName
           ,TABLENAME
           ,RowCount
           ,LastCollectTimeStamp
        FROM DBC.StatsV
        QUALIFY ROW_NUMBER() OVER(
              PARTITION BY DatabaseName,TABLENAME
              ORDER BY CAST(LastCollectTimeStamp AS DATE) DESC
                 ,(CASE WHEN SampleSizePct  IS NULL THEN 100
                        ELSE SampleSizePct  END) DESC
                 ,LastCollectTimeStamp DESC
                 ,RowCount DESC
        ) = 1
        WHERE   DatabaseName = ''
        ) dt1
     ORDER BY 1,2;

Arnie



     
  <Prev Next>   <<First <Prev Next> Last>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 24 Jul 2020