Archives of the TeradataForum

Message Posted: Thu, 24 Jan 2008 @ 18:26:43 GMT

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

Here is a piece of code that can help. It is by no means simple but it does the trick, all you need to do is provide the databasename and the tablename:

     LOCK ROW ACCESS
     SELECT
     databasename,
     tablename,
     columnname,
     statstype
        FROM
          (
           SELECT
             DatabaseName,
             TableName,
             MAX(CASE WHEN ColumnPosition =  1 THEN TRIM(ColumnName) ELSE '' END) ||
             MAX(CASE WHEN ColumnPosition =  2 THEN ',' || TRIM(ColumnName) ELSE '' END) ||
             MAX(CASE WHEN ColumnPosition =  3 THEN ',' || TRIM(ColumnName) ELSE '' END) ||
             MAX(CASE WHEN ColumnPosition =  4 THEN ',' || TRIM(ColumnName) ELSE '' END) ||
             MAX(CASE WHEN ColumnPosition =  5 THEN ',' || TRIM(ColumnName) ELSE '' END) ||
             MAX(CASE WHEN ColumnPosition =  6 THEN ',' || TRIM(ColumnName) ELSE '' END) ||
             MAX(CASE WHEN ColumnPosition =  7 THEN ',' || TRIM(ColumnName) ELSE '' END) ||
             MAX(CASE WHEN ColumnPosition =  8 THEN ',' || TRIM(ColumnName) ELSE '' END) ||
             MAX(CASE WHEN ColumnPosition =  9 THEN ',' || TRIM(ColumnName) ELSE '' END) ||
             MAX(CASE WHEN ColumnPosition = 10 THEN ',' || TRIM(ColumnName) ELSE '' END) ||
             MAX(CASE WHEN ColumnPosition = 11 THEN ',' || TRIM(ColumnName) ELSE '' END) ||
             MAX(CASE WHEN ColumnPosition = 12 THEN ',' || TRIM(ColumnName) ELSE '' END) ||
             MAX(CASE WHEN ColumnPosition = 13 THEN ',' || TRIM(ColumnName) ELSE '' END) ||
             MAX(CASE WHEN ColumnPosition = 14 THEN ',' || TRIM(ColumnName) ELSE '' END) ||
             MAX(CASE WHEN ColumnPosition = 15 THEN ',' || TRIM(ColumnName) ELSE '' END) ||
             MAX(CASE WHEN ColumnPosition = 16 THEN ',' || TRIM(ColumnName) ELSE '' END) ||
             MAX(CASE WHEN ColumnPosition > 16 THEN ',...' ELSE '' END) AS ColumnName,
             'I' AS StatsType,
             /** Floats are stored after the data and data may be 16 or 32 bytes **/
             /** depending on byte[23]**/
             MAX(CASE
                   WHEN SUBSTR(IndexStatistics, 23, 1) = '00'XB THEN 16
                   ELSE 0
                 END) AS Offset,
             MAX(SUBSTR(IndexStatistics, 1, 80)) AS Stats
           FROM
             dbc.indexstats
           GROUP BY
             DatabaseName,
             TableName,
             StatsType,
             IndexNumber
           UNION ALL
           SELECT
             DatabaseName,
             TableName,
             MAX(CASE WHEN ColumnPosition =  1 THEN TRIM(ColumnName) ELSE '' END) ||
             MAX(CASE WHEN ColumnPosition =  2 THEN ',' || TRIM(ColumnName) ELSE '' END) ||
             MAX(CASE WHEN ColumnPosition =  3 THEN ',' || TRIM(ColumnName) ELSE '' END) ||
             MAX(CASE WHEN ColumnPosition =  4 THEN ',' || TRIM(ColumnName) ELSE '' END) ||
             MAX(CASE WHEN ColumnPosition =  5 THEN ',' || TRIM(ColumnName) ELSE '' END) ||
             MAX(CASE WHEN ColumnPosition =  6 THEN ',' || TRIM(ColumnName) ELSE '' END) ||
             MAX(CASE WHEN ColumnPosition =  7 THEN ',' || TRIM(ColumnName) ELSE '' END) ||
             MAX(CASE WHEN ColumnPosition =  8 THEN ',' || TRIM(ColumnName) ELSE '' END) ||
             MAX(CASE WHEN ColumnPosition =  9 THEN ',' || TRIM(ColumnName) ELSE '' END) ||
             MAX(CASE WHEN ColumnPosition = 10 THEN ',' || TRIM(ColumnName) ELSE '' END) ||
             MAX(CASE WHEN ColumnPosition = 11 THEN ',' || TRIM(ColumnName) ELSE '' END) ||
             MAX(CASE WHEN ColumnPosition = 12 THEN ',' || TRIM(ColumnName) ELSE '' END) ||
             MAX(CASE WHEN ColumnPosition = 13 THEN ',' || TRIM(ColumnName) ELSE '' END) ||
             MAX(CASE WHEN ColumnPosition = 14 THEN ',' || TRIM(ColumnName) ELSE '' END) ||
             MAX(CASE WHEN ColumnPosition = 15 THEN ',' || TRIM(ColumnName) ELSE '' END) ||
             MAX(CASE WHEN ColumnPosition = 16 THEN ',' || TRIM(ColumnName) ELSE '' END) ||
             MAX(CASE WHEN ColumnPosition > 16 THEN ',...' ELSE '' END) AS ColumnName,
             'M' AS StatsType,
             /** Floats are stored after the data and data may be 16 or 32 bytes **/
             /** depending on byte[23]**/
             MAX(CASE
                   WHEN SUBSTR(ColumnsStatistics, 23, 1) = '00'XB THEN 16
                   ELSE 0
                 END) AS Offset,
             MAX(SUBSTR(ColumnsStatistics, 1, 80)) AS Stats
           FROM
             dbc.MultiColumnStats
           GROUP BY
             DatabaseName,
             TableName,
             StatsType,
             StatisticsID
           UNION ALL
           SELECT
             DatabaseName,
             TableName,
             ColumnName,
             'C' AS StatsType,
             /** Floats are stored after the data and data may be 16 or 32 bytes **/
             /** depending on byte[23]**/
             CASE
               WHEN SUBSTR(fieldStatistics, 23, 1) = '00'XB THEN 16
               ELSE 0
             END AS Offset,
             SUBSTR(fieldstatistics, 1, 80) AS Stats
           FROM
             dbc.columnstats
           ) dt
         WHERE Stats IS NOT NULL
     and
     tablename=''
     and databasename=''
     ;

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