Re: QUERRY DOUBT..

[Saurabh Sharma]

THANKS A LOT, Seley 
 
the querry works absolutely fine. 
instead i've created a view. which can be easier to querry. and it also let me 
able to devise way for finding each individuals' report.
 
thanks again.
 
saurabh sharma

  - Original Message - 
  From: 
  Seley, 
  Linda 
  To: Multiple recipients of list ORACLE-L 
  Sent: Friday, May 25, 2001 1:51 PM
  Subject: RE: QUERRY DOUBT..
  
  Try 
  this:
   
  select d.owner, 
  d.table_name,b.constraint_name,b.constraint_type,d.column_namefrom 
  all_tables a,all_constraints b,all_cons_columns c, all_tab_columns dwhere 
  d.owner = c.owner (+)and d.table_name = c.table_name (+)and 
  d.column_name = c.column_name (+)and c.table_name = a.table_name 
  (+)and c.table_name = b.table_name (+)and c.constraint_name = 
  b.constraint_name(+)and c.owner = a.owner (+)and c.owner = b.owner 
  (+)order by d.owner, d.table_name, d.column_id;
   
  HTH
   
  Linda
  
-Original Message-From: Saurabh Sharma 
[mailto:[EMAIL PROTECTED]]Sent: Friday, May 25, 2001 12:46 
AMTo: Multiple recipients of list ORACLE-LSubject: 
QUERRY DOUBT..
hi all,
 
i've a querry that is to find 
out what all columns have constraints applied on them. giving the 
table_name,column_name,constraint name and constraint type.
 
the querry goes 
like..
 
select 
a.table_name,b.constraint_name,b.constraint_type,c.column_namefrom 
user_tables a,user_constraints b,user_cons_columns cwhere 
a.table_name=b.table_name 
andb.constraint_name=c.constraint_name/
 
now i need to make a report 
which gives out in the same result all the tables' columns which have 
constraints and which have not, both.
leaving the constraint_type and 
constraint name columns null in the same querry.
is it possible, or do we have 
other alternative to do that.
 
pls suggest.
thanks
 
saurabh
 
 

RE: QUERRY DOUBT..

[Seley, Linda]

Try 
this:
 
select 
d.owner, d.table_name,b.constraint_name,b.constraint_type,d.column_namefrom 
all_tables a,all_constraints b,all_cons_columns c, all_tab_columns dwhere 
d.owner = c.owner (+)and d.table_name = c.table_name (+)and 
d.column_name = c.column_name (+)and c.table_name = a.table_name (+)and 
c.table_name = b.table_name (+)and c.constraint_name = 
b.constraint_name(+)and c.owner = a.owner (+)and c.owner = b.owner 
(+)order by d.owner, d.table_name, d.column_id;
 
HTH
 
Linda

  -Original Message-From: Saurabh Sharma 
  [mailto:[EMAIL PROTECTED]]Sent: Friday, May 25, 2001 12:46 
  AMTo: Multiple recipients of list ORACLE-LSubject: 
  QUERRY DOUBT..
  hi all,
   
  i've a querry that is to find out 
  what all columns have constraints applied on them. giving the 
  table_name,column_name,constraint name and constraint type.
   
  the querry goes 
  like..
   
  select 
  a.table_name,b.constraint_name,b.constraint_type,c.column_namefrom 
  user_tables a,user_constraints b,user_cons_columns cwhere 
  a.table_name=b.table_name 
  andb.constraint_name=c.constraint_name/
   
  now i need to make a report which 
  gives out in the same result all the tables' columns which have constraints 
  and which have not, both.
  leaving the constraint_type and 
  constraint name columns null in the same querry.
  is it possible, or do we have 
  other alternative to do that.
   
  pls suggest.
  thanks
   
  saurabh