Sunday, October 12, 2008

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.

No comments:

Post a Comment