Archives of the TeradataForum

Message Posted: Fri, 18 Dec 2004 @ 07:10:10 GMT

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

This is the first of several queries I created to support physical design. I've had most of these in some form for a number of years, I finally got around to cleaning up the formatting a bit. This query displays one row per index (PI or SI), with output column for each index column.

     /* ############  CROSSTAB OF INDEX COLUMNS PI&SI  ############
     Shows databasename, tablename, index name & type, number of columns and column list.
     good for a quick review of existing indexes.  Don't forget to change the
     databasename in the where clause.

     Author:  Terry Stover, Levi Strauss & Co.  2003-09-14.
              Comments & feedback are welcome: [email protected] or [email protected].

     ############################################################## */

     SELECT  di.databasename, di.tablename, di.indexname, di.indextype,
        di.uniqueflag, max(columnposition) nbr_cols,
        max (case when columnposition = 1 then columnname else '' end)  col1,
        max (case when columnposition = 2 then columnname else '' end)  col2,
        max (case when columnposition = 3 then columnname else '' end)  col3,
        max (case when columnposition = 4 then columnname else '' end)  col4,
        max (case when columnposition = 5 then columnname else '' end)  col5,
        max (case when columnposition = 6 then columnname else '' end)  col6,
        max (case when columnposition = 7 then columnname else '' end)  col7,
        max (case when columnposition = 8 then columnname else '' end)  col8,
        max (case when columnposition = 9 then columnname else '' end)  col9
     FROM dbc.indices di
     WHERE di.databasename in ('dw_data')
     GROUP BY di.databasename, di.tablename, di.indextype, di.indexname, di.uniqueflag
     ORDER BY di.databasename, di.tablename, di.indextype, di.indexname

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