Archives of the TeradataForum

Message Posted: Mon, 25 Sep 2006 @ 20:39:49 GMT

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

I have a SQL that I use to see Stats information for existing tables.

You can use this. It goes database specific.

     /* Single and Multi-Column Indexes */
     SELECT
     DBName
     ,TableName
     ,TBL.IndexNumber
     ,IND1.FieldPosition AS ColumnPosition
     ,TVF1.FieldName AS ColumnName
     ,UPD_TS
     ,CASE WHEN IND1.IndexType = 'P' AND IND1.UniqueFlag = 'Y' THEN 'UPI'
                   WHEN IND1.IndexType = 'P' AND IND1.UniqueFlag = 'N'
     THEN 'NUPI'
                   WHEN IND1.IndexType = 'S' AND IND1.UniqueFlag = 'Y'
     THEN 'USI'
                   WHEN IND1.IndexType = 'S' AND IND1.UniqueFlag = 'N'
     THEN 'NUSI'
     END AS ColumnType

     FROM

     (
     sel
     DB3.DatabaseName as DBName
     ,TVM.TVMName AS TableName
     ,IND.IndexNumber
     ,IND.TableID
     ,IND.IndexType
     ,TVF.FieldName AS ColumnName
     ,IND.FieldPosition AS ColumnPosition

     ,MAX(CASE WHEN TVF.LastAlterTimeStamp > IND.LastAlterTimeStamp
                  THEN TVF.LastAlterTimeStamp
                  ELSE IND.LastAlterTimeStamp END ) OVER(PARTITION BY
     DBName, TableName, IndexNumber, IndexType, IND.UniqueFlag ) AS UPD_TS


     FROM DBC.Indexes IND LEFT JOIN DBC.Dbase DB1
     ON DBC.IND.CreateUID = DB1.DatabaseID

     LEFT JOIN DBC.Dbase DB2
     ON IND.LastAlterUID = DB2.DatabaseID

     JOIN DBC.TVFields TVF
     ON TVF.fieldid = IND.fieldid

     JOIN  DBC.tvm tvm
     ON TVM.tvmid = TVF.tableid

     JOIN DBC.dbase DB3
     ON TVM.DatabaseId = DB3.DatabaseId
     AND     TVM.tvmid = IND.tableid

     WHERE  IND.IndexType <> 'M'
     and DBName = 'pft10_db'
     -- AND TableName = 'COLUMNS'
     AND  (TVF.FieldStatistics IS NOT NULL OR IND.IndexStatistics IS NOT NULL)) TBL

     JOIN DBC.Indexes IND1

     ON TBL.TableID  = IND1.TableID
     AND TBL.IndexNumber = IND1.IndexNumber

     JOIN DBC.TVFields TVF1
     ON TVF1.fieldid = IND1.fieldid
     AND TVF1.TableID = TBL.TableID

     UNION


     -- Single Column

     SELECT
     DB.DatabaseNameI AS DatabaseName
     ,TVM.TVMNameI AS TableName
     ,0 AS IndexNumber
     ,1 AS ColumnPosition
     ,TVF .FieldName AS ColumnName
     ,TVF .LastAlterTimeStamp AS UPD_TS
     ,'COLS' AS ColumnType
     FROM     DBC.TVFields TVF JOIN DBC.TVM TVM
     ON TVM.TVMid = TVF .TableId


     JOIN DBC.Dbase DB
     ON DB.DatabaseId  = TVM.DatabaseId
     WHERE DB.DatabaseNameI = 'pft10_db'
     --AND  TVM.TVMNameI = 'columns'
     AND (DB.DatabaseNameI, TVM.TVMNameI, TVF .FieldName) IS NOT IN (SEL

     DatabaseName

     ,TableName

     , ColumnName

     FROM DBC.Indices Ind

     WHERE Ind.DatabaseName = DB.DatabaseNameI

     AND Ind.TableName = TVM.TVMNameI)
     AND  TVF.FieldStatistics IS NOT NULL

     UNION


     -- MultiColumn
     SELECT
     DB1.DatabaseNameI AS DBName
     ,TVM.TVMNameI AS TableName
     ,Ind.IndexNumber AS IndexNumber
     ,Ind.FieldPosition AS ColumnPosition
     ,TVF.FieldName AS ColumnName
     ,MAX(Ind.LastAlterTimeStamp) OVER(PARTITION BY DBName, TableName,
     IndexNumber) AS UPD_TS
     ,'COLS' AS ColumnType
     FROM DBC.Indexes Ind

     LEFT OUTER JOIN DBC.Dbase DB1
     ON Ind.DatabaseId = DB1.DatabaseID

     JOIN DBC.TVFields TVF
     ON TVF.fieldid = Ind.fieldid

     JOIN DBC.TVM TVM
     ON TVM.TVMid = Ind.tableid
     AND TVM.TVMid = TVF.tableid

     JOIN DBC.DBase DB2
     ON TVM.DatabaseId = DB2.DatabaseId

     WHERE  Ind.Indextype = 'M'
     AND DBName = 'pft10_db'
     --AND TableName = 'columns'
     ORDER BY 1,2,6,3,4;

Thanks,

Vinay Bagare

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