Home » SQL & PL/SQL » SQL & PL/SQL » Query to find table's with primary key (Oracle 11g 11.1.0.6.0 )
Query to find table's with primary key [message #412368] Thu, 09 July 2009 00:10 Go to next message
thisisnatesh
Messages: 92
Registered: March 2007
Member
Hi All,

I want to find list of table names with primary key in my schema.

Please let me know from which table/view i have to find.

Thanks in Advance

Natesh...
Re: Query to find table's with primary key [message #412371 is a reply to message #412368] Thu, 09 July 2009 00:17 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
SQL> desc all_constraints
Re: Query to find table's with primary key [message #412373 is a reply to message #412368] Thu, 09 July 2009 00:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
select * 
from dict 
where table_name like '%CONSTRAINT%' 
   or upper(comments) like '%CONSTRAINT%' 
/

Regards
Michel
Re: Query to find table's with primary key [message #412376 is a reply to message #412373] Thu, 09 July 2009 00:44 Go to previous messageGo to next message
thisisnatesh
Messages: 92
Registered: March 2007
Member
Thanks for your replies.

This is my solution.

Quote:
SELECT tname
FROM tab
WHERE tabtype = 'TABLE'
MINUS
SELECT DISTINCT table_name
FROM all_constraints
WHERE owner = 'SCHEMA_NAME'
AND constraint_type IN ('P');


Regards
Re: Query to find table's with primary key [message #412414 is a reply to message #412368] Thu, 09 July 2009 04:01 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
I thought you wanted a list of tables with a primary key. That gives you the list of tables without one.

Re: Query to find table's with primary key [message #412421 is a reply to message #412368] Thu, 09 July 2009 04:35 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

 select distinct table_name from user_Constraints where constraint_type='P';


Can we use this ??
Re: Query to find table's with primary key [message #412426 is a reply to message #412368] Thu, 09 July 2009 05:09 Go to previous message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
Since tables can only have one primary key the distinct isn't necessary.
Previous Topic: Query
Next Topic: Insert statement
Goto Forum:
  


Current Time: Thu Dec 08 14:06:55 CST 2016

Total time taken to generate the page: 0.24956 seconds