OraFAQ Forum: SQL & PL/SQL » Finding a column or table name when you dont know the location

Home » SQL & PL/SQL » SQL & PL/SQL » Finding a column or table name when you dont know the location

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Finding a column or table name when you dont know the location [message #180794]

Wed, 05 July 2006 05:18

LXC3076
Messages: 8
Registered: June 2006

Junior Member

I am a functional member of the comunity, however I have what seems an easy technical question please can so Embarassed

meone help me.

I have oracle 9i database
The Oracle database has a schema called apps
I want to be able to find any column or table name without specifying the full column name or table...(As I might not know them)

I remember seeing a query which a dba used which went something like this

Select * from user tab columns
where user tab table name is like ¨%AR%CUST%´

Please can someone help to solve this mystery.

Report message to a moderator

Re: Finding a column or table name when you dont know the location [message #180797 is a reply to message #180794]

Wed, 05 July 2006 05:24

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

You can find all the columns whose name contains the string CUST with this query:

SELECT table_name,column_name
FROM   all_tab_columns
WHERE  column_name like '%CUST%';

You can find all the tables whoose name contains the string CUST with this query:

SELECT table_name
FROM   all_tables
WHERE  table_name like '%CUST%'

I an intrigued - what community are you a functional member of, and what would a dysfunctional member look like?

Report message to a moderator

Re: Finding a column or table name when you dont know the location [message #180799 is a reply to message #180794]

Wed, 05 July 2006 05:28

rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India

Senior Member

You are almost near to the answer ...

SELECT * FROM  USER_TABLES WHERE TABLE_NAME LIKE 'E%P%'
 
SELECT * FROM  USER_TAB_COLS WHERE COLUMN_NAME LIKE '%DEPT%'

Rajuvan.

[Updated on: Wed, 05 July 2006 05:29]

Report message to a moderator

Re: Finding a column or table name when you dont know the location [message #181101 is a reply to message #180794]

Thu, 06 July 2006 15:08

Bill B
Messages: 1971
Registered: December 2004

Senior Member

user XXX_tables and XXX_tab_columns where XXX is one of the following.

DBA - Every table in the database, only viewable by DBA level users
ALL - All tables that the current user has access to in any schema.
USER - All tables in the current schema.

Report message to a moderator

Re: Finding a column or table name when you dont know the location [message #181350 is a reply to message #180794]

Sat, 08 July 2006 20:44

PRABURAJ
Messages: 7
Registered: June 2006

Junior Member

Hai,
I hope you are in APPS,Functional consultant.I have some functional doubts and may i know your module specialization.
Regards
Prabu

Report message to a moderator

Previous Topic:	Non-Unique Index
Next Topic:	remove chars from string

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Aug 03 05:35:57 CDT 2025