Query to find Tables in Voyager with a specific field (column)
- Product: Voyager
- Relevant for Installation Type: Multi-Tenant Direct, Dedicated-Direct, Local, TotalCare
Question
I want to find all the Tables in Access that have an "item type" column in the table. How might I do that?
Answer
The following Access Pass-Through query is one way to do this. It allows you to dump the information directly from the Oracle Data Dictionary.
You will need to edit this query to include your correct database name (replace XXXDB with your database name).
The query results include TABLE_NAME and COLUMN_NAME.
select *
from all_tab_columns
where owner = 'XXXDB'
and column_name like '%ITEM%TYPE%'
and data_type = 'NUMBER'
order by table_name, column_name
;
There is a lack of standardization in column names. Some have "_ID" in them, others do not. That's why we are being sort of "vague" (in computer-speak: "fuzzy") in the string we are searching for in our "like" statement. You will need to look at the information in the tables to make sure you understand the data.
In the above query, the filtering of the data_type field to "NUMBER" makes sure the field is numerical, which is what ITEM_TYPE is - a numeric field.
Constructing custom SQL queries by request and troubleshooting unexpected results from customer-created SQL queries falls outside the scope of Support. The above has been posted for informational purposes. Voyager-L and Developer Network are useful resources for finding helpful custom SQL or obtaining assistance from peers in troubleshooting custom queries.
- Article last edited: 08-Apr-2022