Home Page for the TeradataForum
 

Archives of the TeradataForum

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


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


Subj:   Useful Data Dictionary Index Queries - Index Column Crosstab
 
From:   Terry Stover

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: tstover@levi.com or terry.stover@comcast.net.

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

     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>>
 
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016