Archives of the TeradataForum

Message Posted: Tue, 02 Dec 2003 @ 12:54:45 GMT

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

Hi Sodhi,

May be something like the below sql could help you out. You may do some R&D and modify the below SQL for the other DBC stats views columnstats & multicolumnstats (new in v2r5).

From your requirements I also think that you need to gerenalize this SQL for all the tables in your database too.

SELECT
   'COLLECT STATISTICS ON db_name.t_name INDEX ('
  || (max (case when ColumnPosition = 1 then trim (both from ColumnName) end))
  || (coalesce (max (case when ColumnPosition =  2 then  ', ' || trim (both from ColumnName) end),  ''))
  || (coalesce (max (case when ColumnPosition =  3 then  ', ' || trim (both from ColumnName) end),  ''))
  || (coalesce (max (case when ColumnPosition =  4 then  ', ' || trim (both from ColumnName) end),  ''))
  || ');'  (title '')
FROM DBC.IndexStats
WHERE (DatabaseName, TableName,  IndexNumber) IN (
   SELECT DatabaseName, TableName,  IndexNumber
   FROM   DBC.IndexStats
   WHERE  DatabaseName = db_name AND Tablename = t_name AND
          IndexStatistics IS NOT NULL
   GROUP BY DatabaseName, TableName,  IndexNumber
)
GROUP BY IndexNumber
HAVING count (*) > 1
ORDER BY IndexNumber
;

Thanks, Vivek.

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