Search an Oracle Database for Tables with Specific Column Names?

Searching multiple column names simultaneously

To seek multiple column names within tables at once, you can adhere to the IN operator. Let's assume, for instance, that you're seeking tables containing columns 'COL1', 'COL2', 'COL3', and 'COL4'. And you strictly need those tables that possess all four columns.

-- The SQL equivalent of a full house in poker!
SELECT owner, table_name 
FROM all_tab_columns 
WHERE column_name IN ('COL1', 'COL2', 'COL3', 'COL4') 
GROUP BY owner, table_name 
HAVING COUNT(DISTINCT column_name) = 4;


Conducting a case-insensitive search: Upper function

In Oracle, column names are typically uppercase. However, if your database uses mixed or lowercase letters for column names, you need a case-insensitive approach. In this case, utilize the UPPER function to battle against the forces of case-sensitivity.

-- Oracle: WHERE IS 'Col1'? I ONLY KNOW 'COL1'!
SELECT owner, table_name 
FROM all_tab_columns 
WHERE UPPER(column_name) IN (UPPER('Col1'), UPPER('Col2'));


Remember to include the owner field to discriminate schemas.

Accessing Views and Partial Name Matches: LIKE Operator

You may require a broader search span that can seize any partial column name matches. LIKE operator is a fantastic tool for this task. It is associated with the % wildcard that replaces any number of characters.

-- Hunting for column-name breadcrumbs!
SELECT owner, table_name FROM all_tab_columns WHERE column_name LIKE '%ART%';


Use select distinct to ensure you don't get duplicate table names.

Grand Search: UNION Operator

The UNION operator is used to combine the result-set of two or more SELECT commands. In our case, it can be utilized to include Oracle views in the search, thereby providing a holistic picture.

-- It's a bird! It's a plane! It's... all your relevant metadata!
SELECT 'Table' AS Type, owner, table_name
FROM all_tab_columns WHERE column_name = 'TARGET_COLUMN'
UNION
SELECT 'View', owner, view_name
FROM all_views WHERE column_name = 'TARGET_COLUMN';


Here, we have not only tables but also views that contain your TARGET_COLUMN.

Metadata exploration: data types and sizes

Sometimes, it's not merely about the names. You might also require data types and sizes of these columns. Applying the below query, you can get all the relevant information:

-- Show me all your secrets, column!
SELECT owner, table_name, column_name, data_type, data_length
FROM all_tab_columns
WHERE column_name = 'TARGET_COLUMN';


This query returns a comprehensive idea about the nature of your columns.

Utilizing different metadata views

Oracle provides alternative metadata views such as dba_tab_columns or user_tab_columns that accord different views according to access privileges.

• DBA_TAB_COLUMNS caters to a system-wide view for privileged users.
• USER_TAB_COLUMNS is limited to tables owned by the current user.

Comprehensive Search with Unified Results

When working with multiple schemas, you might need a unified list of all relevant tables across these schemas that possess your TARGET_COLUMN. You can achieve that by performing the following:

-- One ring to rule them all, one ring to find them.
SELECT DISTINCT owner, table_name 
FROM all_tab_columns 
WHERE column_name = 'TARGET_COLUMN' 
ORDER BY owner, table_name;


This command gives one entry per distinct table across schemas, all neatly organized.