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:
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.
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 MetadataTo 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.