Archives of the TeradataForum

Message Posted: Thu, 08 Sep 2011 @ 13:39:50 GMT

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

This might do what you want...

Ulrich

     WITH RECURSIVE col_list
     (DatabaseName, TableName, IndexNumber, Columnlist, ColumnPosition,  maxpos)
     as
     (
       SELECT DatabaseName,
                 TableName,
                 IndexNumber,
                 cast(trim(ColumnName) as varchar(1000)) as  Columnlist,
                 ColumnPosition,
                 max(columnposition) over (partition by DatabaseName, TableName,
     IndexNumber) as maxpos
       FROM DBC.Indices
       where indextype = 'S'
       and uniqueflag = 'N'
      qualify columnposition = 1
       UNION ALL
       SELECT b.databasename,
              b.tablename,
              b.indexnumber,
              b. Columnlist !! ',' !! trim(c.columnname) as  Columnlist,
              c.columnposition,
              b.maxpos
      FROM DBC.Indices c
             JOIN
             col_list b
             ON c.databasename = b.databasename
                    and c.tablename = b.tablename
                    and c.indexnumber = b.indexnumber
                    and c.columnposition = b.columnposition + 1
              where c.ColumnPosition < 999
         )
         SELECT 'collect stats on ' !! trim(databasename) !! '.' !!
     trim(tablename) !! ' index (' !! Columnlist!! ');'
             FROM col_list
             where columnPosition = maxpos
             ORDER BY 1;

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