Catalog Your SQL Scripts with MS Access

February 8, 2001

Down the code for this article


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.
 


See All Articles by Columnist Danny Lesandrini









The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers