469,331 Members | 1,183 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,331 developers. It's quick & easy.

EXISTS

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
1 13313
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.

Similar topics

8 posts views Thread by Trev | last post: by
15 posts views Thread by Geiregat Jonas | last post: by
18 posts views Thread by Dan | last post: by
4 posts views Thread by DEWright_CA | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by haryvincent176 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.