Archives of the TeradataForum

Message Posted: Tue, 05 Dec 2007 @ 01:32:59 GMT

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

I don't have a query handy, but I suggest you look at dbc.indices. Columns of interest are the databasename, columnname, indextype (P = primary). You might also be interested in the indextype (UPI or NUPI).

In terms of comparison, have a look at the minus operator. It is like union, except instead of combining rows from two queries, it removes them. Thus

     Select /* index details you are interested in */
     >From dbc.indices
     Where databasename = 'a'
     Minus
     Select /* index details you are interested in */
     >From dbc.indices
     Where databasename = 'b'

Will give you the differences from 'b' to 'a'.

Note that you have to run the query twice - the second swaps the 'a' and 'b' to give you differences from 'a' to 'b'.

Thus you want to run this:

     Select /* index details you are interested in */
     >From dbc.indices
     Where databasename = 'a'
     Minus
     Select /* index details you are interested in */
     >From dbc.indices
     Where databasename = 'b';
     Select /* index details you are interested in */
     >From dbc.indices
     Where databasename = 'b'
     Minus
     Select /* index details you are interested in */
     >From dbc.indices
     Where databasename = 'a';

I'll leave it you to fill in the rest.

Glenn Mc

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