How to search through a table in SQL to " identify and list " which tables have a specified column name

How to search through a table in SQL to " identify and list " which tables have a column named "JOB_ID" in them?

select table_name,column_name from (
  select table_name,
    column_name,
    EXTRACTVALUE(
      DBMS_XMLGEN.getxmltype(
        'select count(*) X from ' || table_name || ' where to_char(' || column_name || ') = ''8083'' and rownum=1'
      ),
      '/ROWSET/ROW/X'
    ) count
  from All_tab_cols
  where owner ='JAS' and table_name LIKE 'JOB%' and column_name = 'JOB_ID'
  order by table_name, column_name
)
where count=1
/

Open in new window


That code returns this....finally learning a little here fella's This is super cool:
However there are actually 24 tables with the name JOB% so I guess the error comes when the code does not see a table with JOB_ID in it?
Because the list below shows only 14 JOB tables with the JOB_ID in the table.
If that is correct then how do I check for those that do not contain JOB_ID and ignore that so no errors occur?
1      JOBS      JOB_ID
2      JOB_BILLINGS_VIEW      JOB_ID
3      JOB_CONTACTS      JOB_ID
4      JOB_DETAILS      JOB_ID
5      JOB_DETAILS_SUM_VIEW      JOB_ID
6      JOB_DIVISIONS      JOB_ID
7      JOB_EXPENSES      JOB_ID
8      JOB_LABOUR_SUMMARY_VIEW      JOB_ID
9      JOB_MATERIAL_SUMMARY_VIEW      JOB_ID
10      JOB_MAT_ORDERS_BY_FLOOR      JOB_ID
11      JOB_SECTIONS      JOB_ID
12      JOB_SUMMARY_VIEW      JOB_ID
13      JOB_SUPPLIERS      JOB_ID
14      JOB_VIEW      JOB_ID
RUA Volunteer2?Tableau Trainer & Consultant Sales Exec.Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
select table_name from all_tab_columns where column_name='JOB_ID';

or the view you are using in all of your previous questions:
select table_name from all_tab_cols where column_name='JOB_ID';
0
SujithData ArchitectCommented:
I have already posted a modified query to your other post using all_tab_columns.
0
slightwv (䄆 Netminder) Commented:
and, you are likely getting an error because you have removed the VARCHAR2 and NUMBER data type check form your previous questions.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

RUA Volunteer2?Tableau Trainer & Consultant Sales Exec.Author Commented:
This code returned 14 table results below In your opinion does it look accurate. On another note it occurred to me if I was looking for all of the JOB tables that contain JOB_ID how do I list only those that do not contain JOB_ID. I have been trying to get that for the last hour and cannot seem to get it to work. Used (NOT IN ,  <> , NOT =, NOT EQUAL , NOT LIKE ....non of which worked?)

select table_name,column_name from (
  select table_name,
    column_name,
      case 
    when data_type in ('VARCHAR2', 'NUMBER') then 
    EXTRACTVALUE(
      DBMS_XMLGEN.getxmltype(
        'select count(*) X from ' || table_name || ' where to_char(' || column_name || ') = ''8083'' and rownum=1'
      ),
      '/ROWSET/ROW/X'
    ) end count
  from All_tab_cols atc inner join all_objects ao on atc.table_name = ao.object_name
    Where atc.owner ='JAS' and ao.owner ='JAS'
       and table_name LIKE 'JOB%' 
     and   exists (select 1 from all_tab_columns atc where atc.table_name = atc.table_name and atc.column_name = 'JOB_ID')
  order by table_name, column_name
)
where count=1
/

Open in new window


         TABLE_NAME      COLUMN_NAME
1      JOBS      JOB_ID
2      JOB_BILLINGS_VIEW      JOB_ID
3      JOB_CONTACTS      JOB_ID
4      JOB_DETAILS      JOB_ID
5      JOB_DETAILS_SUM_VIEW      JOB_ID
6      JOB_DIVISIONS      JOB_ID
7      JOB_EXPENSES      JOB_ID
8      JOB_LABOUR_SUMMARY_VIEW      JOB_ID
9      JOB_MATERIAL_SUMMARY_VIEW      JOB_ID
10      JOB_MAT_ORDERS_BY_FLOOR      JOB_ID
11      JOB_SECTIONS      JOB_ID
12      JOB_SUMMARY_VIEW      JOB_ID
13      JOB_SUPPLIERS      JOB_ID
14      JOB_VIEW      JOB_ID
0
Mark GeerlingsDatabase AdministratorCommented:
I think this simple query will give you exactly what you asked for:

select table_name,column_name
from All_tab_columns
where owner ='JAS' and table_name LIKE 'JOB%' and column_name = 'JOB_ID'
order by table_name, column_name;
0
Mark GeerlingsDatabase AdministratorCommented:
"how do I list only those that do not contain JOB_ID"?
Then you need to include a "not exists" subquery like this:
select table_name,column_name
 from All_tab_columns
 where owner ='JAS' and table_name LIKE 'JOB%'
  and  not exists ( select t2.table_name
    from All_tab_columns t2
  where t2.owner ='JAS' and t2.table_name LIKE 'JOB%' and t2.column_name = 'JOB_ID')
 order by table_name, column_name;
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SujithData ArchitectCommented:
If you are looking to list tables that do not contain the column JOB_ID -

select  table_Name
from    all_tables ut
where   owner       = 'JAS'
and     table_name  like 'JOB%'
and     not exists (select 1 
                    from all_tab_columns atc 
                    where atc.table_name = ut.table_name and atc.column_name = 'JOB_ID')
;

Open in new window

0
SujithData ArchitectCommented:
>> This code returned 14 table results below In your opinion does it look accurate.
We do not have access to your schema or data. The query meets the requirements you have stated so far, hence the output should be ok.
0
slightwv (䄆 Netminder) Commented:
I don't see how limiting the query to tables with a specific column helps in your overall master plan that you have been working towards.  So the requirement has changed to locate any table and column that contains any value and for a specific job_id?

In other words:  I want to know what tables are related to job_id=123 that have the word 'pencil' in some other column?


Since it is your question:
As I have mentioned in several of your previous related questions:  You are making this much harder than it needs to be.

I see no need to join to all_objects and I see no need for an EXISTS and another query to all_tab_columns.

all_tab_columns and all_tab_cols are basically the same view.  Pick one. stick with it and forget the other view exists.

If you insist on doing it all, you need to pay attention to the view alias you use and the scope where you use it.

For example:
from All_tab_cols atc inner join all_objects ao on atc.table_name = ao.object_name
    Where atc.owner ='JAS' and ao.owner ='JAS'
       and table_name LIKE 'JOB%'
     and   exists (select 1 from all_tab_columns atc where atc.table_name = atc.table_name and atc.column_name = 'JOB_ID')
 

In the exists part of the query, Oracle has no way to know what ATC.TABLE_NAME you are using since the alias is the same so it is using the one in the inner select itself.  So, it isn't doing what you think you want it to do.

>>column_name,       case      when data_type in ('VARCHAR2', 'NUMBER') then

Sujith, I really wish you would explain how moving the data type check to a CASE over in the where clause makes any difference.  If you explained that in the other question, I missed it.
0
SujithData ArchitectCommented:
@slightwv - unless I am missing something really obvious, here is the demo

SQL> desc tbl11
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 USERID                                                         BLOB

SQL> select table_name,column_name from (
  2    select table_name,
  3      column_name,
  4      EXTRACTVALUE(
  5        DBMS_XMLGEN.getxmltype(
  6          'select count(*) X from ' || table_name || ' where to_char(' || column_name || ') = ''8083'' and rownum=1'
  7        ),
  8        '/ROWSET/ROW/X'
  9      ) count
 10    from All_tab_cols atc
 11      Where atc.owner ='MAC'
 12         and table_name LIKE 'TBL11%'
 13         and data_type in ('VARCHAR2', 'NUMBER')
 14    order by table_name, column_name
 15  )
 16  where count=1
 17  /
      DBMS_XMLGEN.getxmltype(
      *
ERROR at line 5:
ORA-19202: Error occurred in XML processing
ORA-00932: inconsistent datatypes: expected NUMBER got BLOB
ORA-06512: at "SYS.DBMS_XMLGEN", line 288
ORA-06512: at line 1


SQL> select table_name,column_name from (
  2    select table_name,
  3      column_name,
  4        case
  5      when data_type in ('VARCHAR2', 'NUMBER') then
  6      EXTRACTVALUE(
  7        DBMS_XMLGEN.getxmltype(
  8          'select count(*) X from ' || table_name || ' where to_char(' || column_name || ') = ''8083'' and rownum=1'
  9        ),
 10        '/ROWSET/ROW/X'
 11      ) end count
 12    from All_tab_cols atc
 13      Where atc.owner ='MAC'
 14         and table_name LIKE 'TBL11%'
 15    order by table_name, column_name
 16  )
 17  where count=1
 18  /

no rows selected
                                                                                                       

Open in new window

0
RUA Volunteer2?Tableau Trainer & Consultant Sales Exec.Author Commented:
Here is what I am doing and have done with all the related questions. I have thanks to this group and EE built 30-40 really well executing queries that will help me accomplish and find a variety of things. I have also gotten to see what I need to construct to some degree the code needed at least in Oracle SQL to build the SQL Grep too I have bored you all with. My point is that all the variations that you have helped me with are being used.  My confusion occurs when there is some term I do not know or understand. Thanks to you all. I know what to look for because your code is so good even during confusion I get a chance to see the code look up the terms I do not know and realize how this all works. It is fantastic. My goals are to create new business and new jobs. This is helping tremendously.

This is spot on.
"In other words:  I want to know what tables are related to job_id=123 that have the word 'pencil' in some other column?" Correct !

select table_name,column_name from (
  select table_name,column_name,
   case 
    when data_type in ('VARCHAR2', 'NUMBER') then 
    EXTRACTVALUE(
      DBMS_XMLGEN.getxmltype(
        'select count(*) X from ' || table_name || ' where to_char(' || column_name || ') = ''&SEARCH_RECORD_VAR'' and rownum=1'
      ),
      '/ROWSET/ROW/X'
    ) end count
  from All_tab_cols atc inner join all_objects ao on atc.table_name = ao.object_name
  where atc.owner ='JAS' and ao.owner ='JAS'
  and table_name LIKE '&TABLE_NAME_VAR%' 
  and exists (select 1 from all_tab_columns atc where atc.table_name = atc.table_name and atc.column_name = '&Search_Column_Name')
  order by table_name, column_name
)
where count=1
/

Open in new window


This worked really well too!
select  table_Name
from    all_tables ut
where   owner  = 'JAS'
and     table_name  like '&Search_Table'
and     not exists (select 1 
                    from all_tab_columns atc 
                    where atc.table_name = ut.table_name and atc.column_name = '&Not_Column_Name')

Open in new window

0
slightwv (䄆 Netminder) Commented:
>>unless I am missing something really obvious, here is the demo

There is no way a table with a single BLOB column will be returned based on that where clause.  I'm also not seeing how a column with a data type other than those two can be returned.

SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
PL/SQL Release 12.2.0.1.0 - Production
CORE    12.2.0.1.0      Production
TNS for 64-bit Windows: Version 12.2.0.1.0 - Production
NLSRTL Version 12.2.0.1.0 - Production

SQL> select table_name from dba_tables where table_name LIKE 'TBL11%';

TABLE_NAME
--------------------------------------------------------------------------------
TBL11

SQL> drop table tbl11 purge;

Table dropped.

SQL> create table tbl11(userid blob);

Table created.

SQL>
SQL> desc tbl11
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 USERID                                             BLOB

SQL>
SQL> select table_name,column_name from (
  2     select table_name,
  3         column_name,
  4         EXTRACTVALUE(
  5           DBMS_XMLGEN.getxmltype(
  6             'select count(*) X from ' || table_name || ' where to_char(' || column_name || ') = ''8083'' a
nd rownum=1'
  7           ),
  8           '/ROWSET/ROW/X'
  9         ) count
 10       from All_tab_cols atc
 11         Where
 12            table_name LIKE 'TBL11%'
 13            and data_type in ('VARCHAR2', 'NUMBER')
 14       order by table_name, column_name
 15     )
 16     where count=1
 17     /

no rows selected

SQL>

Open in new window

0
slightwv (䄆 Netminder) Commented:
>>"In other words:  I want to know what tables are related to job_id=123 that have the word 'pencil' in some other column?" Correct !

I would urge you to keep your end game in mind and the hole you are digging.

Next requirement will be:  Tables that start with 'J' that have a column named 'BOB' and tables that have an 'E' somewhere in the name and a column named 'FRED' and any table created on a Wednesday....and so on...

You will get into a dynamic SQL nightmare that will also likely be open to SQL injection.

>>My goals are to create new business and new jobs.

Noble goal but I ask you think about this:
If SQL Grep existed and doesn't today, why not?
If a tool like it was necessary and filled some niche market, why hasn't SQL Grep been replaced yet?
If I thought it was a niche, why would I or one of the other Experts where write it?

Learning is ALWAYS good.  I hope you keep the goals within reach.
0
SujithData ArchitectCommented:
@slightwv - I suspect it is bug fixed in 12c. Fletcher was facing it too.
0
SujithData ArchitectCommented:
@Fletcher - the latest query you have posted have a number of issues. slightwv had mentioned some of them in a previous post. You need to pay attention to each change you make. Table aliases etc are quite important.

You have already asked for quite a lot of variations of the same question and have got answers for the same. I hope you observe where the changes are being made and use that learning to explore and solve your problems further.

See a corrected version

select table_name,column_name from (
  select table_name,column_name,
   case 
    when data_type in ('VARCHAR2', 'NUMBER') then 
    EXTRACTVALUE(
      DBMS_XMLGEN.getxmltype(
        'select count(*) X from ' || table_name || ' where &Search_Column_Name=123 and to_char(' || column_name || ') = ''&SEARCH_RECORD_VAR'' and rownum=1'
      ),
      '/ROWSET/ROW/X'
    ) end count
  from All_tab_cols atc1
  where atc1.owner ='JAS' 
  and table_name LIKE UPPER('&TABLE_NAME_VAR%' )
  and exists (select 1 from all_tab_columns atc2 where atc1.table_name = atc2.table_name and atc2.column_name = UPPER('&Search_Column_Name'))
  order by table_name, column_name
)
where count=1
/

Open in new window

0
slightwv (䄆 Netminder) Commented:
>>@slightwv - I suspect it is bug fixed in 12c. Fletcher was facing it too.

Not likely but if it was, change to the all_tab_columns view.  That would be a pretty huge bug.

What version are you running?

sqlfiddle still has 11g around and it doesn't repeat the issue.
http://sqlfiddle.com/#!4/c07e7/4

Please post a complete test case I can run somewhere...

>>See a corrected version

Still over complicated.  No need for the exists and select against the same view.
0
slightwv (䄆 Netminder) Commented:
Anyway Fletcher, if you want to write a tool to do what you suggested in your original question with an attempt at selling it, you want to do, DO NOT use this approach.  It was a way to quickly get you what you originally posted without writing any code!!!  It was never meant to be efficient.

You are now hacking at the SQL in an attempt to make it more efficient by making it a LOT MORE complex.  I am afraid that you will not win that fight.

I would also expect a tool such as this to search more than two basic data types.

Remember, all the code I've posted in all your questions stemmed from a question asked here years ago and the SQL has been posted in many places.
0
SujithData ArchitectCommented:
@slightwv - I am able to reproduce this issue in sqlfiddle, which is running on 11.2.0.2.

I am on 11.2.0.4. Switching to all_tab_columns doesn't fix the issue.

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

SQL> desc tbl11
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 VAL                                                            BLOB

SQL>
SQL> select table_name,column_name from (
  2    select table_name,
  3      column_name,
  4      EXTRACTVALUE(
  5        DBMS_XMLGEN.getxmltype(
  6          'select count(*) X from ' || table_name || ' where to_char(' || column_name || ') = ''8083'' and rownum=1'
  7        ),
  8        '/ROWSET/ROW/X'
  9      ) count
 10    from All_tab_columns atc
 11      Where atc.owner ='MAC'
 12         and table_name LIKE 'TBL11%'
 13         and data_type in ('VARCHAR2', 'NUMBER')
 14    order by table_name, column_name
 15  )
 16  where count=1
 17  /
      DBMS_XMLGEN.getxmltype(
      *
ERROR at line 5:
ORA-19202: Error occurred in XML processing
ORA-00932: inconsistent datatypes: expected NUMBER got BLOB
ORA-06512: at "SYS.DBMS_XMLGEN", line 288
ORA-06512: at line 1


SQL>
SQL> select table_name,column_name from (
  2    select table_name,
  3      column_name,
  4        case
  5      when data_type in ('VARCHAR2', 'NUMBER') then
  6      EXTRACTVALUE(
  7        DBMS_XMLGEN.getxmltype(
  8          'select count(*) X from ' || table_name || ' where to_char(' || column_name || ') = ''8083'' and rownum=1'
  9        ),
 10        '/ROWSET/ROW/X'
 11      ) end count
 12    from All_tab_columns atc
 13      Where atc.owner ='MAC'
 14         and table_name LIKE 'TBL11%'
 15    order by table_name, column_name
 16  )
 17  where count=1
 18  /

no rows selected
                                                                                                      

Open in new window

0
slightwv (䄆 Netminder) Commented:
>>I am able to reproduce this issue in sqlfiddle, which is running on 11.2.0.2.

Please provide me the link so I can run it as well.

What do you get with:
select table_name, column_name
from All_tab_columns
Where owner ='MAC'
 and table_name LIKE 'TBL11%'
  and data_type in ('VARCHAR2', 'NUMBER');
0
slightwv (䄆 Netminder) Commented:
@Fletcher,
I forget if you ever provided a database version but if you are still on 11g, I would suggest moving off of it.  It is pretty much end of life.
0
RUA Volunteer2?Tableau Trainer & Consultant Sales Exec.Author Commented:
BANNER      CON_ID
1      Oracle Database 12c Release 12.1.0.1.0 - 64bit Production      0
2      PL/SQL Release 12.1.0.1.0 - Production      0
3      "CORE      12.1.0.1.0      Production"      0
4      TNS for Linux: Version 12.1.0.1.0 - Production      0
5      NLSRTL Version 12.1.0.1.0 - Production      0
0
slightwv (䄆 Netminder) Commented:
Not sure what was causing the error you were getting several questions back.

Anyway, If you want a quick and dirty way to get what you want from your original question then I would go back to the simple version of what I posted many questions ago.

If you still get errors, we can address those but you changed the SQL between tests within the same question.  It makes debugging difficult.

If you are still looking to come up with a new version of SQL Grep, please abandon the entire concept of using DBMS_XMLGEN.  It will not work for you in the long run.
0
sdstuberCommented:
Fletcher,

You seem to be confusing people with this question, which is apparently one in a chain of other questions, and in this one you have changed not only the sql but also the goal more than one time.  Simultaneously, you are also referencing previous questions which some may and some may not have ever seen.  

Each question on EE should be able to stand on its own.  My suggestion to you would be to create a schema, maybe more than one if necessary, populate it with tables, and populate some or maybe all of those tables with data.

Then post the ddl and sql for those schemas, objects, and data  and follow it up with the set results you are expecting.

Once you do that everyone will be able to participate in a meaningful way and not guess.

Once you have your answer, if you decide it's not sufficient because your example wasn't complete, close the question and open a new one.  Copy all of the ddl and sql for your example into the new question and then change/extend it with your new requirement.

If this sounds like a lot of work, it's not.  It should not take more than half an hour to come up with a self-contained test case as I have described it.  That half an hour of work will likely save you hours, if not days of back and forth confusion with the participants here.

It might be a cliche, but it's also good advice:  "Help us to help you"
2
RUA Volunteer2?Tableau Trainer & Consultant Sales Exec.Author Commented:
I see what I have done with these questions. I have a similar problem when I work with a client who wants a Crystal Report that does 90 different operations dynamically all in one report. When really it is a series of reports. Most clients don't get that. I am in the same boat here. Learning how something works and asking the questions like How to search a series of tables that contain a specific field/column in it. Then I on my own started (spent an hour plus trying with all the wrong syntax) trying to make the same query that does what I asked it to do, now do the "Inverse" of that. Show all tables without the same field/column.  I have confused things here and to the guys I am sorry. Your right it is good advice. I get the confusion honestly.  I grew up on a Texas Ranch and Farm so I have shoveled a lot of manure, bailed a lot of hay so I have no problem with doing the work.

I assume you are talking about using a tool like SQL Fiddle...? I am learning to create table .....never done that before. If you have any suggestions on tools..? I know excel access SAP many CRMs Salesforce Crystal Reports Tableau and a few others. I have worked with several different tools like TOAD years ago and MY SQL. Nothing that tough. Simple simple stuff. Nothing complicated. So if you have suggestions on any specific tools or simple things I can learn on.....all ears. Thanks again for the advice.
0
slightwv (䄆 Netminder) Commented:
>>I am learning to create table .....never done that before. If you have any suggestions on tools..?

You have a database.  If not, get one.  Oracle XE is 100% free for everything and the rest of the products are pretty much free for education and development.  There is a 30 day clause in the fine print but...

SQL Developer is also free and a GUI that has a lot of features.

Personally, I like sqlplus which is a straight command line.  Not the best for the GUI generation but I'm an old dog!

Learn the syntax from the docs.  How you enter it really doesn't matter.
0
RUA Volunteer2?Tableau Trainer & Consultant Sales Exec.Author Commented:
I am not sure what is going on with my closing of these questions. I closed this weeks ago I thought. I am not sure if it is internet connectivity or what but I closed this thinking it was done. Sorry. I went back and did the best I could to award points accordingly. I had a hard time following the logic of my own question.  That is embarrassing...!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PL/SQL

From novice to tech pro — start learning today.