On the move:

I'm currently moving the following to their own blogs: Unix, HTML for Blogs, Perl & Java.
The links are on the left side.

Tuesday, December 22, 2009

Finding Names in SQL Queries

(This is an update to a previously posted query.) This code will help document a SQL query as it looks through that code to find the list of tables, columns, amper-variables, and other called queries referenced. I added the following to the older version: called queries, a reference to the query name in the spooled file, and also to the spool filename.

There are some limitations in this code:
  • It does not handle 'Select *'. You'll just have to search for these for now -- maybe fixed in the future.
  • It only ignores comments that begin in column 1, therefore, if your comment starting in column 30 said "Deleted Dept_No," your report would show that name. (A future fix.)
As shown in the following example, the SomeQuery.SQL (on the left) generates the report on the right.

This code was long enough that I've stored it as 'NamesInCode.SQL'. You'll have to scroll down the page to see the file, and download button.

No comments:

Post a Comment