All Forums Database
rtefft 11 posts Joined 03/09
08 Apr 2009
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

adhiayl 1 post Joined 04/09
08 Apr 2009

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.

Adeel Chaudhry 773 posts Joined 04/08
09 Apr 2009

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

-- If you are stuck at something .... consider it an opportunity to think anew.

rtefft 11 posts Joined 03/09
09 Apr 2009

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

You must sign in to leave a comment.