Question: cleaning up PK indexes

Hi, all.

I've been tasked with doing some index cleanup, and am looking for a way to quantify my task.

My db has about 450 tables in it. In some of these tables, the PK index is actually a UNIQUE INDEX PK_TBLNAME, rather than a CONSTRAINT PK_TBLNAME PRIMARY KEY. These still work, but they're not correct.

The only way I can verify the distinction here is in the create statement for the index. In TOAD, the script information is built on-the-fly, so I'm not sure if the text of the create statement is stored anywhere or not.

What I'd like to do is something like SELECT SEGMENT_NAME FROM SYS.DBA_SEGMENTS WHERE SEGMENT_TYPE = 'INDEX' and SEGMENT_SCRIPT LIKE '%UNIQUE INDEX%'. I know that SEGMENT_SCRIPT is not part of that view, but this is what I want to find.

I don't want to have to plod through 450 scripts in TOAD to find the offending indexes...

Any suggestions would be appreciated.

Thanks!

DW Received on Fri Apr 29 2005 - 16:15:02 CDT