Catalog Your SQL Scripts with MS Access

Work Smarter … not Harder

Information is the lifeblood of an organization, and it’s no different here at
Quizno’s.   Store information, guest
trends, mailing lists … all sorts of data are necessary to run a business. Accordingly, our application development department is frequently asked to run “ad hoc” queries against the database and deliver the results in an Excel spreadsheet to the end user.
It seems that the same requests are made on a regular basis, but often, the SQL used to
generate the data is either lost or misplaced. Thus, programmers are wasting valuable
time searching through long lists of ambiguously named sql files or worse yet, reinventing
the proverbial SQL wheel.

This problem can be reduced or eliminated entirely by following these suggestions:

1. Define a realistic and user-friendly naming scheme for saved SQL queries
2. Standardize query metadata by using a ‘Comment Block’ template
3. Create a utility to search query SQL file names and metadata

Define a Naming Scheme for Saved SQL Queries

Our first order of business was to agree on a naming scheme for saving .sql files.
After some discussion we agreed that a file name should begin with a 3-letter
identifier describing its type. The prefixes we settled on and their associated
definitions are listed below. Your mileage with this convention may vary.

• adh = Ad Hoc Query for Report
• dml = UPDATE, INSERT, DELETE …
• ddl = CREATE, DROP, GRANT …
• usp = User Stored Procedures
• asp = Application Stored Procedures

In our shop, the next most memorable piece of information about ad hoc sql is the
Run Date. Users are likely to say something like, “Would you please run that mailing list
query you did for me last October.” … like I can remember what I did yesterday, let alone
last October. Accordingly, run date is added to the filename in the format
yyyymmdd.

Next comes the name of the person who requested the information. We’ve chosen to use the same
format that our system admin uses when assigning network login names: last name and first initial.
The last component of our standard file name is the query name, which we try to keep brief, but
descriptive.

Following this format provides a way to quickly scan the list of .sql files while in Windows
Explorer by type and Run Date. Consider some examples in the screen shot below.

Standardize Query Metadata

To help developers stick to this format, a template was created which can be pasted into SQL Server
Query Analyzer and edited . By consistently using this template (described below) a VBA
string parsing routine can be used to extract all of the metadata contained in the comment block
header section. This metadata can then be imported and stored in an Access database and searched
on any or all of the categories described below:

QUERY COMMENT TEMPLATE:

    /*
      Filename:       adh_20010208_HRDepartment_FirstQuarterMailingList                 
      Author:         LesandriniD
      Query Type:     adh
      Run Date:       2001-02-08
      Requested By:   HRDepartment
      Query Name:     FirstQuarterMailingList
      Constraints:    Date Range Jan 1, 2001 to Mar 31, 2001
      Formulas:       none
      Description:    Open Store Mailing List for all 1st Quarter recipients
    */

The code for parsing the above text is not complicated, but is beyond the scope of this
article. You can download my utility that goes along with
this article and view the code. It’s not always pretty, but it gets the job done.
Below is a screen shot of the main search page. The Import Query Meta Data button
invokes the interface for locating, parsing and saving a .sql query file. The search
mechanism is designed to provide a filtered list of queries matching your criteria. It’s
a fun little tool that has made me considerably more productive. Download it and give it
a try.

Next time, I’ll expand on one of the really cool functions of this utility, the Execute
Query Into Excel button. Excel automation is used from MS Access to create and populate
a worksheet with the results of your query. I’ve even automated the inclusion of a custom
header and footer, as well as the ability to format columns for dates and zip codes.