Archives of the TeradataForum

Message Posted: Tue, 26 Sep 2006 @ 12:42:35 GMT

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

Hi,

Use this query to generate the SQL for Collect Stat. Just copy the output of the Text Field and paste it in SQL Assistance and run it.

Hope this helps.

     SELECT TableName, Text
     FROM
     (
     SELECT
     TableName
     , IndexNumber
     ,RANK() OVER (PARTITION BY TableName, IndexNumber ORDER BY
     ColumnPosition) Seq_Num
     ,CASE WHEN Seq_Num = 1
     THEN 'COLLECT STAT ON ' || TRIM(DatabaseName) || '.' || TRIM(TableName)
     || ' INDEX ( ' || ColumnName
     ELSE
     ', ' || ColumnName
     END AS Text
     FROM DBC.INDICES
     WHERE DatabaseName = 
     UNION
     SELECT TableName, IndexNumber, 100+CSUM(1,1), ');'
     FROM
     (
     SELECT DISTINCT TableName, IndexNumber FROM DBC.INDICES
     WHERE DatabaseName = 
     ) A
     ) IDX
     Order BY TableName,IndexNumber, Seq_Num;

Kind Regards,

Muhammad Affan

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