LIst all tables with the column name I specify..

[patnim17]

Hi,
How can I find the list of all tables in my schema, that has a column whose name contains, say SSN, ?

is there a way I can find it.

pat

 

[LKBrwnDBA]

Look at table USER_TAB_COLUMNS

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[mashelford]

for tables:
SELECT TABLE_NAME,COLUMN_NAME
FROM FND_TABLES, FND_COLUMNS
WHERE FND_TABLES.TABLE_ID=FND_COLUMNS.TABLE_ID
AND FND_COLUMNS.COLUMN_NAME LIKE'%SSN%'
ORDER BY table_name ASC

For Views:

SELECT view_NAME,COLUMN_NAME
FROM FND_views, FND_view_COLUMNS
WHERE FND_views.VIEW_ID=FND_view_COLUMNS.VIEW_ID
AND FND_view_COLUMNS.COLUMN_NAME LIKE'%SSN%'
ORDER BY view_NAME ASC

 

[SantaMufasa]

Whoa, Mashelford, none of the "FND..." objects you mention above are part of Oracle's data dictionary. One could certainly use your technique against DBA_..., ALL_..., or USER_TAB_COLUMNS (as LKBrwnDBA suggested), but those "FND..." objects would be something that a user would have to create her/himself, right?

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.