Is there a way (SQL) we can get information as we get from Teradata Administrator to check statistics information. Sample output from TD Administrator
Date Time Unique Values Column Names 06/04/01 08:24:09 5 C_ENT_TYPE
I looked up Manuals and found a SQL which gives me part of this information. I also know that the underlying DBC tables used are Indexes and TVFields.
The problem arises if we need to look up a Multi-Column Stats information. Do you know a easy way to find this.
I wanted to generate a report to get the statistical information on a daily basis. I can get Statistics information for Indexes (Single/Multi Column) and Single Column Indexes. I need some help on Multi-Column Stats (Non-indexe columns).
Thanks for sharing the information. I also wanted the stats date. I actually used the base View definition of MultiColumnStats to come up with this SQL. I can capture the last refresh time here as well. I think this information would be sufficient.
Thanks for directing me to the view. Never thought about looking at the view definition itself.
SELECT DBC.DBase.DatabaseNameI(NAMED DatabaseName), DBC.TVM.TVMNameI(NAMED TableName), DBC.Indexes.IndexNumber(NAMED StatisticsId), DBC.Indexes.FieldPosition(NAMED ColumnPosition,FORMAT 'Z9'), DBC.TVFields.FieldName(NAMED ColumnName), DBC.Indexes.IndexStatistics(NAMED ColumnsStatistics), DBC.Indexes.LastAlterTimeStamp FROM DBC.Indexes LEFT OUTER JOIN DBC.Dbase DB1 ON DBC.indexes.DatabaseId = DB1.DatabaseID, DBC.dbase, DBC.TVM, DBC.TVFields WHERE DBC.TVM.DatabaseId = DBC.dbase.DatabaseId
AND DBC.TVM.TVMid = DBC.indexes.tableid AND DBC.TVM.TVMid = DBC.TVFields.tableid AND DBC.indexes.indextype = 'M' AND DBC.TVFields.fieldid = DBC.indexes.fieldid and db1.DatabaseNameI = 'DW_SUPPORT_DB' order by 3,4;