By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,323 Members | 1,216 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,323 IT Pros & Developers. It's quick & easy.

EXISTS

P: n/a
I want to know the column names that exists in more than one table;
also, I want to know the table names where it exists. I tried this
query and it took a long time and so I stopped it:

select table_name,column_name, data_length, data_type from
user_tab_cols
where column_name in (select column_name from user_tab_cols
group by column_name
having count (*) > 1)
order by column_name

When I try using EXISTS instead of IN, I get wrong results; it gives
me the the column names even when it exists in only one table.
select table_name,column_name, data_length, data_type from
user_tab_cols
where exists (select column_name from user_tab_cols
group by column_name
having count (*) > 1)
order by column_name

Please let me know what I am doing wrong? I am not very familiar with
EXISTS

Thank you in advance.
Jul 19 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
hi,
here is the possible solutions

select
table_name,
column_name,
data_length,
data_type
from
user_tab_cols A
where exists (select column_name
from user_tab_cols
where column_name = a.column_name
group by column_name
having count (*) > 1)
order by column_name


select distinct
a.table_name,
a.column_name,
a.data_length,
a.data_type
from
user_tab_cols a,
user_tab_cols b
where
a.column_name = b.column_name and
a.data_length = b.data_length and
a.data_type = b.data_type and
a.table_name <> b.table_name
order by column_name, table_name;
Jul 19 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.