Identify unique indexes/keys using DBC views

Database
N/A

Identify unique indexes/keys using DBC views

I am building a query on DBC.Columns to provide a more readable report on our table structures. As part of this, I am trying to include an indicator to identify table columns which are part of a unique primary index or primary key on the table. I tried DBS.Indices, but find no rows in it for any of my database tables (even though many have unique primary indexes). I read the manuals, and DBC.Indices looks like the right place but I see no data in it. Any help is appreciated.

Thanks,
Rich

Rich
3 REPLIES

Re: Identify unique indexes/keys using DBC views



Hi Rich,

Try using the TRIM function in your filters (databasename, tablename). May not be the reason but its these small things that usually gives you results you don't want (in your case no result at all).

You should be looking for the value 'P' (for PI) or 'K' (for PK) in the IndexType column.

Also, If your table has PI (col a, col b) and is partitioned on col c, the IndexType will be 'Q' (viz. partitoned Primary Index) for Col a and Col b and there will be no mention of Col c.

Thanks,
Gautam.
Teradata Employee

Re: Identify unique indexes/keys using DBC views

Hello,

Yes, you are looking in a right view, just verify if it does have rows in it.

Rest, as suggested by Gautam, use TRIM on DatabaseName, TableName, IndexName and ColumnName and also if you are doing join on any of these columns.

For all possible values of IndexType column, manuals will be your best bet! :)

HTH!

Regards,

Adeel

N/A

Re: Identify unique indexes/keys using DBC views


Since Unique Primary Indexes were used, I found them where IndexType='P' and UniqueFlag='Y'. I hadn't quite understood how TD indexes/constraints things compare to Oracle (my background).

Thanks for the help,
R