Archives of the TeradataForum

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

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

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