Oninit cdi / Archive
Talk to an expert →

find lack of PKs

Posted in 2006

Topics: General Discussion

ROBERT ROUSSEY — — source: IIUG Forums & Mailing Lists

I need a quick-and-dirty (query? myschema?) way to find all the tables in a db WITHOUT a unique index or PK. Suggestions? TIA Bob Roussey

↪ replying to ROBERT ROUSSEY
Paul Watson — — source: IIUG Forums & Mailing Lists

Select tabname from systables Where tabid not in (select tabid from sysindexs where idxtype = U ) Or something similar Paul Watson Tel: +44 1414161772 Mob: +44 7818003457 GO FURTHER with DB2 GET THERE FASTER with Informix. Attend the IDUG 2006 North America Conference. Tampa, Florida, USA. 7-11 May 2006. Visit http://www.iiug.org/conf for more information. > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On > Behalf Of ROBERT ROUSSEY > Sent: Thursday, January 26, 2006 4:33 PM > To: ids@iiug.org > Subject: find lack of PKs [6278] > > > I need a quick-and-dirty (query? myschema?) way to find all > the tables in a db > WITHOUT a unique index or PK. Suggestions? > > TIA > > Bob Roussey > > > ************************************************************** > ***************** > Forum Note: Use "Reply" to post a response in the discussion forum. >

↪ replying to ROBERT ROUSSEY
Kern Doe — — source: IIUG Forums & Mailing Lists

This query seems to do just what you need, you may want to look at sysindexes part and set additional condition as you wish -- let me know if I may have missed anything, anyone? Kern -- {this produces a list of tables that don't have index} select tabname from systables where tabid > 99 and tabtype = 'T' and tabid not in (select tabid from sysindexes) ROBERT ROUSSEY <robert.roussey@spiritair.com> wrote: I need a quick-and-dirty (query? myschema?) way to find all the tables in a db WITHOUT a unique index or PK. Suggestions? TIA Bob Roussey ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

↪ replying to Kern Doe
ART KAGEL, BLOOMBERG/ 731 LEXIN — — source: IIUG Forums & Mailing Lists

Almost. You are finding tables with no indexes which is far more than was asked. A table can only have a primary or unique key if it also has a unique index. So modify your subquery to include "WHERE idxtype = 'U'" and you've got it. My sense of efficiency wants to flatten the correlated subquery into a join, but the engine will do that for you, so... Art ----- Original Message ----- From: Kern Doe <ids@iiug.org> At: 1/26 17:56 This query seems to do just what you need, you may want to look at sysindexes part and set additional condition as you wish -- let me know if I may have missed anything, anyone? Kern -- {this produces a list of tables that don't have index} select tabname from systables where tabid > 99 and tabtype = 'T' and tabid not in (select tabid from sysindexes) ROBERT ROUSSEY <robert.roussey@spiritair.com> wrote: I need a quick-and-dirty (query? myschema?) way to find all the tables in a db WITHOUT a unique index or PK. Suggestions? TIA Bob Roussey ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.