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 count(*) X from ' || table_name || ' where to_char(' || column_name || ') = ''8083'' and rownum=1'
where owner ='JAS' and table_name LIKE 'JOB%' and column_name = 'JOB_ID'
order by table_name, column_name
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