Finding Names in Code

This query will find column, table and variable names. The column and table name are based on the data dictionary; variable names are anything starting with '&'.

For example, the following code will generate the report as shown:Because of the length, the code is located on my web site as an attachment named NamesInCode.Sql.

• It gets the name of a query from the user.


• Creates an external table to read that file.


• Creates a second external table of the words in that file if not a comment.


• Creates a global temporary table of the intersection of table names from All_Tab_columns and the words.


• Spools that list of table names.


• Based on the table names, compiles a list of column names from All_Tab_Columns and compares that to the words.


• Spool words found as column_names.


• Spool words found that start with an ampersand.

Note: There is a new version of this query that includes other called queries.