Find Tables Containing a Specific Column in Oracle Database

calendar Mar 6, 2024 / · 9 min read · Oracle SQL Query dba_tab_columns all_tab_columns  ·
Share on: linkedin copy

Find Tables Containing a Specific Column in Oracle Database

Purpose

Which tables in this database have a column called CUSTOMER_ID? Sooner or later every DBA or developer needs the answer. The DBA_TAB_COLUMNS view lists the columns of every table, view, and cluster in the database. One query against it returns each owner and table that has a column with the name you give.

This helps in many daily tasks. You may need to see how tables relate through a shared key. You may plan to rename or drop a column and want to know where it lives. You may be checking that a column such as a status flag exists in every schema that should have it. The query below does the job from any SQL*Plus session.

The script asks for the column name when it runs. You type the name, and it prints one row for each match, with the owner and table joined into one readable value.

Code

1set pages 999 lines 100
2col tab format a60
3col column_name format a20
4select owner || '.' || table_name as tab
5, column_name
6from dba_tab_columns
7where column_name like upper('&col')
8/

Code Breakdown

Display settings

  • set pages 999 sets the number of lines on each page of output to 999. With a large result, the column headings then repeat less often.
  • lines 100 sets the total number of characters that SQL*Plus shows on one line before it starts a new line. Raise the value if your table names are long and the output wraps.
  • col tab format a60 sets the width of the tab column to 60 characters. The a means alphabetic text, and the number is the width. Raise the width if your names are longer.
  • col column_name format a20 sets the width of the column_name column to 20 characters. If a value does not fit, SQL*Plus wraps or truncates it, depending on the SET WRAP setting.

The query

  • owner || '.' || table_name as tab joins the owner and the table name with a dot. The two vertical bars are the string join operator. The result reads like HR.EMPLOYEES, which you can paste straight into another statement.
  • column_name shows which column matched. This matters when you use wildcards, because several different names can match one pattern.
  • from dba_tab_columns is the data dictionary view that holds the data. It describes the columns of all tables, views, and clusters in the database.
  • where column_name like upper('&col') filters the rows. The next section explains each part.

The filter

  • &col is a substitution variable. SQL*Plus replaces it with the text you enter before it sends the statement to the database.
  • upper(...) changes the text to upper case. Oracle reads a name without double quotes as upper case, so the dictionary holds most column names in upper case. The upper call lets you type customer_id and still match CUSTOMER_ID.
  • like matches a pattern instead of an exact value. The percent sign % matches any string of zero or more characters. The underscore _ matches exactly one character. So %ID finds every column that ends in ID.
  • The slash on the last line tells SQL*Plus to run the statement.

Key Points

  • The view covers more than tables. DBA_TAB_COLUMNS describes the columns of tables, views, and clusters. A match can therefore be a view, not a table. Check the object type in a second step if that matters to you.
  • Quoted names do not match. If someone created a column with double quotes and mixed case, such as "CustomerId", the stored name keeps its case. The upper call turns your input into CUSTOMERID, and that does not match. Quoted identifiers are case sensitive.
  • The underscore is a wildcard. Many column names contain an underscore, such as CUSTOMER_ID. In a like pattern the underscore matches any one character, so CUSTOMER_ID can also match a name like CUSTOMERXID. Use an escape character when you need an exact match.
  • Hidden columns are left out. DBA_TAB_COLUMNS filters out system-generated hidden columns. The related view DBA_TAB_COLS does not filter them.
  • You need access to the DBA views. If your account cannot query DBA_TAB_COLUMNS, use ALL_TAB_COLUMNS. It shows only the objects that the current user can access.

Insights and Best Practices

Choose the right view for your privileges

Oracle gives the same data in three scopes. DBA_TAB_COLUMNS covers the whole database. ALL_TAB_COLUMNS covers the tables, views, and clusters that the current user can access. USER_TAB_COLUMNS covers the objects the current user owns, and it does not display the OWNER column.

The columns of DBA_TAB_COLUMNS are the same as those of ALL_TAB_COLUMNS, with one exception: SENSITIVE_COLUMN. So you can switch the view name in the script and the rest still works. Here is the same query for an account without DBA access:

1select owner || '.' || table_name as tab
2, column_name
3from all_tab_columns
4where column_name like upper('&col')
5/

Keep in mind that this version may miss tables you cannot see. A short result list does not prove that no other table has the column.

Search with wildcards

Because the filter uses like, you do not need the full name. These inputs all work at the &col prompt:

  • CUSTOMER_ID finds that exact name (with the underscore caveat above).
  • %ID finds all names that end in ID.
  • CUST% finds all names that start with CUST.
  • %DATE% finds all names that contain DATE.

For an exact match on a name with an underscore, add an escape clause. Any character can be the escape character except the percent sign and the underscore. When the escape character comes before % or _, Oracle reads that character as plain text and not as a wildcard.

1select owner || '.' || table_name as tab
2, column_name
3from dba_tab_columns
4where column_name like upper('&col') escape '\'
5/

At the prompt you would then enter CUSTOMER\_ID.

Control how SQL*Plus handles the variable

SQL*Plus normally prints the old and new text of a statement when it replaces a substitution variable. SET VERIFY OFF stops that listing.

You can also define the value before you run the script. The DEFINE command names a substitution variable and gives it a CHAR value. For example, define col = CUSTOMER_ID sets the value, and the query then uses it without asking again. If you use a double ampersand, &&col, SQL*Plus keeps the value and does not prompt again in the same session until you run UNDEFINE col. That is useful when you run several queries for the same column.

Be careful with variable names. Oracle warns that if a value you supply matches the name of a defined variable, SQL*Plus uses the content of that variable instead. Pick a variable name that is unlikely to match real input.

Narrow the result by schema

On a large database, one column name such as ID or CREATED can match thousands of rows. Add a filter on owner to limit the result:

1select owner || '.' || table_name as tab
2, column_name
3from dba_tab_columns
4where column_name like upper('&col')
5and owner = upper('&owner')
6/

The owner column holds the owner of the table, view, or cluster. This form gives a short list that is easy to read.

When to Use This Check / When to Run This

  • Before you rename, drop, or change the data type of a column and need a full list of places that use it.
  • When you join an unfamiliar schema and want to find the tables that share a key column.
  • During a migration, to confirm that an audit column or a status column exists in every table that should have it.
  • When a developer reports an error that names a column, and you need to know which table owns it.
  • When you document a database and want to map a business term, such as EMAIL, to the tables that store it.
  • Before you add a new column, to check whether the name is already in use somewhere else.

Troubleshooting Common Issues

The query returns no rows, but you know the column exists. Check the case. If the column was created with double quotes and mixed case, the upper call hides it. Search with a pattern like %CustomerId% against the stored name, without the upper call. Also check your privileges. If you query ALL_TAB_COLUMNS, you only see objects you can access.

SQL*Plus returns an error that the table or view does not exist. Your account cannot read DBA_TAB_COLUMNS. Ask for access, or switch to ALL_TAB_COLUMNS or USER_TAB_COLUMNS. With USER_TAB_COLUMNS, remove owner from the select list, because that view does not display it.

The output wraps or looks broken. Raise the lines value, or lower the widths in the col commands. If a value does not fit the width you set, SQL*Plus wraps or truncates it. Remember that a width shorter than the column heading truncates the heading.

Too many rows come back. Use a more exact pattern, add an owner filter as shown above, or use the escape form to stop the underscore from matching other characters.

A match is a view, not a table. DBA_TAB_COLUMNS includes views and clusters. Look up each match by name if you need to confirm that it is a table.

References

Posts in this series