Archives of the TeradataForum

Message Posted: Fri, 13 Jan 2012 @ 15:49:12 GMT

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

sobhan.k.pratim wrote:

          > select distinct  b.TVMNameI  TABLE_NAME
          >   from DBC.TVFields  a
          > inner join DBC.TVM b
          > on a.Tableid = b.TVMid
          > AND b.TableKind = 'T'
          > and a.FieldStatistics  is null
          > order by 1

This will return some false positives: there might be multicolumn stats/indexes.

A simple brute force method:

     select databasename, tablename
     from dbc.Tables
     where TableKind = 'T'

     except

     select databasename, tablename
     from dbc.IndexStats
     where IndexStatistics is not null

     except

     select databasename, tablename
     from dbc.MultiColumnStats
     where ColumnsStatistics is not null

     except

     select databasename, tablename
     from dbc.ColumnStats
     where FieldStatistics is not null

Or of course my StatsInfo view :-)

     select DatabaseName, TableName, CollectStatement from StatsInfo qualify
        min(MissingStats)
        over (partition by DatabaseName, TableName) = 'Y'
     and StatsType in ('UPI','NUPI')

Dieter

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