Friday, October 22, 2010

Documenting 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.
Posted by Lynn Tobias at 3:20 PM
Labels: , , , , , ,

3 comments:

  1. UnknownMay 17, 2011 at 3:31 AM

    Hi Lynn,

    I find your blog really interesting. I got some new ideas in Oracle SQL side. I am also working on something to parse SQL query. Thanks for the start.

    Regards,
    Sarath

    ReplyDelete
  2. BettyJune 27, 2011 at 3:40 AM

    I will try this query to see the exact results as I am not cleared with your explanation. I do have understood the limitations of this code but need to know if they are still there in the latest version also or tell me the version in which these limitations reflect.
    sap upgrade automation

    ReplyDelete
  3. Lynn TobiasJuly 10, 2011 at 10:16 PM

    Betty, I wrote this under 10g, and haven't tried it under 11. It worked for everything I tried it on. Let me know if you have problems. Lynn

    ReplyDelete

Subscribe to: Post Comments (Atom)