Locate a field 2

[sxschech]

Describe Table X will give me the list of the fields, can I "query" on this so that I only see those fields that contain '%Course%' for example.

Also, If I know the name of a field, is there a query that can list out all the tables that contain that field. Example CourseNo is in Table X, Y, Z, and G.

 

[SantaMufasa]

Sure, sxschech, Following is my script, "show_tabs_with_col_name.sql" that shows tables and their owner(s) that contain whatever column string for which you are looking. (You cannot just copy and paste this code since it contains an "ACCEPT...PROMPT" code fragment -- you must save the code to a script file, then invoke the script as I have done here:

@show_tabs_with_col_name
Enter all, or a portion, of the column name for which you are searching: last_name

TABLE_NAME                     COLUMN_NAME
------------------------------ ---------------
DHUNT.CUSTOMER                 LAST_NAME
DHUNT.CUSTOMER2                LAST_NAME
TESTNEW.S_EMP                  LAST_NAME
TEST.ABOVE_AVG                 LAST_NAME
TEST.EMP_REC                   LAST_NAME

Here is the source code for the "show_tabs_with_col_name.sql" script:

accept col_in prompt "Enter all, or a portion, of the column name for which you are searching: "
select owner||'.'||table_name table_name
      ,column_name
  from all_tab_columns
 where column_name like upper('%&col_in%')
/

Let us know if you have questions.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Beilstwh]

Dave,
When I answer a user, I normally just give them the guery they are looking for. When you do, you give them complete utilities. Have a star for your "above and beyond" support of the users.

Bill
Oracle DBA/Developer
New York State, USA

 

[SantaMufasa]

Very gracious, Bill. Thanks.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[sxschech]

Here is my star for you too. You've been most helpful. Was busy, so couldn't respond sooner to try your script.

 

[jimirvine]

Dave,
as always, an excellent and helpful thread. Can I stick my big nose in however and add a possible addendum to your code?
Might I suggest incorporating bind variables...

accept col_in prompt "Enter all, or a portion, of the column name for which you are searching: "
var x varchar2(30)

exec :x :=  '&col_in'

select owner||'.'||table_name table_name
      ,column_name
  from all_tab_columns
 where column_name like '%'||upper(:x)||'%';