All Forums Database
BBR2 96 posts Joined 12/04
21 Apr 2006
Collect Stats Report

All,Is there a way (SQL) we can get information as we get from Teradata Administrator to check statistics information. Sample output from TD AdministratorDate Time Unique Values Column Names06/04/01 08:24:09 5 C_ENT_TYPEI 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,Vinay

Sathish 3 posts Joined 11/05
22 Apr 2006

Try :select * from dbc.multicolumnstats where tablename='XX' and databasename='XX'Thanks.

BBR2 96 posts Joined 12/04
22 Apr 2006

Sathish,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.TVFieldsWHERE DBC.TVM.DatabaseId = DBC.dbase.DatabaseIdAND DBC.TVM.TVMid = DBC.indexes.tableidAND DBC.TVM.TVMid = DBC.TVFields.tableidAND DBC.indexes.indextype = 'M'AND DBC.TVFields.fieldid = DBC.indexes.fieldid and db1.DatabaseNameI = 'DW_SUPPORT_DB'order by 3,4;Vinay

You must sign in to leave a comment.