Home » SQL & PL/SQL » SQL & PL/SQL » retrieve list of tables with same name from two schemas
retrieve list of tables with same name from two schemas [message #595946] Mon, 16 September 2013 09:28 Go to next message
sweety2811
Messages: 4
Registered: August 2013
Location: USA
Junior Member
Hi All,


I need to retrieve the list of tables with same name from 2 different schemas.Someone please help me in getting a query for this requirement.Thanks in advance.
Re: retrieve list of tables with same name from two schemas [message #595947 is a reply to message #595946] Mon, 16 September 2013 09:32 Go to previous messageGo to next message
John Watson
Messages: 4686
Registered: January 2010
Location: Global Village
Senior Member
One approach would be a compound query against dba_tables. You would want the intersection of a query with the first schema used as a predicate and a query with the second schema used as a predicate.
Re: retrieve list of tables with same name from two schemas [message #595949 is a reply to message #595947] Mon, 16 September 2013 09:44 Go to previous messageGo to next message
sweety2811
Messages: 4
Registered: August 2013
Location: USA
Junior Member
I have used the below query but I am not sure if it is correct:

SELECT TABLE_NAME FROM DBA_TABLES WHERE OWNER= 'HST'
INTERSECT
SELECT TABLE_NAME FROM DBA_TABLES WHERE OWNER= 'MRT'
Re: retrieve list of tables with same name from two schemas [message #595951 is a reply to message #595949] Mon, 16 September 2013 09:49 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2445
Registered: May 2013
Location: World Wide on the Web
Senior Member
Please use code tags.

Check if this works for you -

SELECT A.OWNER SCHEMA_1, B.OWNER SCHEMA_1, A.TABLE_NAME
  FROM DBA_TABLES A, DBA_TABLES B
 WHERE A.OWNER = 'HST'
   AND B.OWNER = 'MRT'
   AND A.TABLE_NAME = B.TABLE_NAME;


Re: retrieve list of tables with same name from two schemas [message #595952 is a reply to message #595951] Mon, 16 September 2013 09:54 Go to previous messageGo to next message
sweety2811
Messages: 4
Registered: August 2013
Location: USA
Junior Member
@ john ,Lalith : Thankyou so much

@Lalith: The query which i posted and the one which you asked to try gave the same results though.Thankyou again
Re: retrieve list of tables with same name from two schemas [message #595955 is a reply to message #595952] Mon, 16 September 2013 10:17 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2445
Registered: May 2013
Location: World Wide on the Web
Senior Member
sweety2811 wrote on Mon, 16 September 2013 20:24


@Lalith: The query which i posted and the one which you asked to try gave the same results though.Thankyou again


It's Lalit not Lalith Smile

Yes, the results out to be similar using the INTERSECT set operator or explicit joins I used. Intersect is better here infact.
Re: retrieve list of tables with same name from two schemas [message #595961 is a reply to message #595955] Mon, 16 September 2013 11:38 Go to previous messageGo to next message
Michel Cadot
Messages: 59422
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Intersect is better here infact.


So what is your urge to post this weaker "solution"?
Actually there is NO need to post another "solution".
And there is NO need for you to answer this.

Regards
Michel
Re: retrieve list of tables with same name from two schemas [message #595993 is a reply to message #595961] Tue, 17 September 2013 02:10 Go to previous messageGo to next message
Littlefoot
Messages: 19694
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
OK, but, then again, what is the purpose of such a reply from your side, Michel?

As we've already seen, most problems have several solutions. Some of them are magnificent, some are just good, and some are horribly bad and should be avoided. I think that it doesn't do any harm if we discuss all/some of them, point out what's good and what's bad. For example, in this very case: self-join vs. intersect. Or topic I have just participated in (within our Forms forum) - IF vs. DECODE or CASE. Or recent DML in functions discussion. Not to mention various regular expressions vs. oldfashioned SUBSTR/INSTR problems. I'm sure you could name plenty of them more.

I can't express how glad I am to see Maaher back. Someone who actually says something in his posts. Honestly, I'm getting sick & tired of please read and follow type of messages which, unfortunately, seem to take a large percent of all posted messages. Just look at Maaher's messages. Does he complain about test case? Formatting? Code tags? Nope - he creates his own test case (if he wants to answer the question), formats his own code, encloses it into code tags. He gives an EXAMPLE of how things should be done, doesn't lament about it.

Fortunately, there are some other people here who do the same.

Note that I'm not questioning vast knowledge some of OraFAQ forum members have. I just think that not everything lies in that. Nice approach and attitude make the difference. I know we are all different, but me - I prefer that friendly approach better than kind of hostile one.

Did you notice that there are more and more complaints, posted by relatively new members, saying that they don't like the way they are treated (as if they are 1Di0t5, or however you spell it). So why wouldn't we just make a large banner, saying that people who don't search first aren't welcome. People who don't format their posts aren't welcome either. You don't read documentation? Go away! What next? Gypsies & coloured people stay out? Come on! Scare them all away!

I'm hanging here for years now, and I know at least couple of people who left the forum because they didn't feel comfortable here any more. Not freshmen, no sir - hundreds and thousands of posts in their hands.

And no, I don't expect any kind of an answer to what I just wrote.
Re: retrieve list of tables with same name from two schemas [message #595995 is a reply to message #595993] Tue, 17 September 2013 02:50 Go to previous messageGo to next message
Michel Cadot
Messages: 59422
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Fair enough.

Regards
Michel
Re: retrieve list of tables with same name from two schemas [message #596006 is a reply to message #595993] Tue, 17 September 2013 04:48 Go to previous messageGo to next message
pablolee
Messages: 2655
Registered: May 2007
Location: Scotland
Senior Member
+1 LF
Re: retrieve list of tables with same name from two schemas [message #596316 is a reply to message #595951] Fri, 20 September 2013 04:01 Go to previous message
Maaher
Messages: 7054
Registered: December 2001
Senior Member
Lalit Kumar B wrote on Mon, 16 September 2013 16:49
Please use code tags.

Check if this works for you -

SELECT A.OWNER SCHEMA_1, B.OWNER SCHEMA_1, A.TABLE_NAME
  FROM DBA_TABLES A, DBA_TABLES B
 WHERE A.OWNER = 'HST'
   AND B.OWNER = 'MRT'
   AND A.TABLE_NAME = B.TABLE_NAME;
Here's another approach:
Select table_name
From   dba_tables
Where  owner in ('HST', 'MRT')
Group  By table_Name Having count(*) = 2
/


Like Littlefoot said: there are often several ways to get a result.

MHE

[Updated on: Fri, 20 September 2013 04:02]

Report message to a moderator

Previous Topic: Need XML query for tree
Next Topic: Please help me to close the IF blocks.
Goto Forum:
  


Current Time: Thu Oct 23 15:44:57 CDT 2014

Total time taken to generate the page: 0.11777 seconds