It is essential to understand the details of the databases underlying the applications you work with; to know what happens to which tables when you perform an action in your application. No matter how slick your SQL skills are, walking into a new situation with unfamiliar databases is only going to make that task trickier; and most large companies – and many small ones, too – will have a heterogeneous database environment, unless you are very fortunate. Mixtures of SQL Server and Oracle are fairly common, and every database type will have different structures and different syntax to remember.
Why do you need Multi-DataBase Query Templates?
Whether you know your SQL syntax backwards and have a bag of uncommon tricks, or just have a good working knowledge of SQL, you will eventually have a time-consuming task fine-tuning your queries or looking up obscure code. This is a hassle on it’s own, but to really understand any database environment you need to know more than just the data; you need the meta-data as well.
Data queries can answer questions like “Who are the best performing sales people for the quarter?”, but meta-data queries (or meta-queries for short) return information about the data in your databases, such as “What are the primary key to foreign key relationships on a given field, and do they all use the same name?“. Unlike data queries, meta-queries are (thankfully) completely generic – if properly parameterized – no matter how your database or enterprise is structured. They are, however, usually very different for different database types. You can read about turning the QueryPicker into a fully-fledged Query Template Explorer over here, or find out more about the the QueryPicker’s inner LINQ workings over here.
Imagining QueryPicker
Laziness
“The quality that makes you go to great effort to reduce overall energy expenditure. It makes you write labour-saving programs that other people will find useful, and document what you wrote so you don’t have to answer so many questions about it.”
– Larry Wall, Programming Perl
Trying to pull this information out of hundreds of tables in SQL Server, Oracle and later MySQL posed a nice challenge for me. Although all of these relational databases share a core syntactic structure, it’s still incredibly time consuming to be constantly flipping through reference manuals looking for system-specific meta-queries. To handle all these database types in a uniform manner, without having to remember three sets of arcane incantations, I abstracted the process to the point that I could focus on the questions, rather than the queries. I wanted to get to the point where I could simply say, “For SQL Server, show me the tables with columns meeting criteria x, y, and z“, and any complexity in the SQL dialects became irrelevant. And since meta-queries are by their nature generic, you can immediately try out the examples in this article and put them to use. You can also use the template library supplied with the QueryPicker right out-of-the-box. Once you learn the principles and the data structures involved you can easily apply them to your data queries to build up a useful library for a data template explorer as well.
My specific design goals for this project were that it should:
- Handle multiple database types.
- Have an extensible list of templates to choose from.
- Have a user-friendly way to fill in place holders.
- Grant or suppress access to the finished query.
- Execute the query and get a result set.
In this first of a two-part series, I’ll describe what the QueryPicker is and give you enough particulars to experiment with it, including a demo program. In the second part of this series I will explain the details of how to work with it as a .NET developer, incorporating the QueryPicker and possibly other supplied building blocks into your own applications.
Off the Shelf
SQL Server already has a template facility built into SQL Server Management Studio (SSMS). In fact, this has existed at least as far back as SQL Server 2000, with Query Analyzer. On the other hand, Oracle seems to offer no support at all for query templates from its SQL Developer tool (as of version 1.5.1). Squarely in the middle of these options is the popular SQLyog GUI interface for MySQL, providing a limited template explorer and preview facility (as of version 8.0). Unfortunately, SQLyog has no concept of active place holders, and thus no mechanism for replacing them (it’s possible that other GUIs for MySQL may offer better support).
Individual features aside, the main stumbling block here is that each tool is database type-specific, and so they don’t support my first key design goal. They do have a few nice features, though:
With SSMS, you have a user-friendly template explorer pane, containing a categorized navigation tree of available query templates. With the chosen template in your edit buffer, you can invoke a simple form to specify parameter replacement values. Each parameter placeholder has a <name, type, default_value> structure, and SSMS scans the template for these to generate a template-specific value input form. Once you’re satisfied with the parameter values, your template is smoothly converted into a normal query. The templates are also conveniently stored as individual files in a directory tree, making it simple to extend the list.

Figure 1. Filling in a Template (with SSMS) to generate a finished query.
Conversely, the template explorer from the popular SQLyog tool displays the list of queries in a flat list, so finding just what you want is less convenient than a categorized tree. On the other hand, it does have a very convenient template preview-simply selecting a template in the list displays the template in the preview pane before you insert it into your editor.

Figure 2. Query Template Explorer (SQLyog for MySQL) and Template Explorer (SSMS)
For my own needs I have developed a .NET user control combining the best parts of these tools while adding other useful features, and bundled this into my freely available open-source libraries. I’ll delve into the details of how ‘QueryPicker’ works now, but feel free to use the building blocks I provide.
From the Database to the Application
QueryPicker is aimed more at the Database Developer audience than the DBA audience, primarily because it’s mostly designed to examine meta-data. This is partly due to the fact that you can write very generic meta-data queries, and partly because I think meta-data is fascinating and essential for understanding your environment.
Using Meta-Query Templates
Each meta-query is separately specified for each supported database type (currently SQL Server, Oracle, and MySQL). Some-such as identifying the database version-require no input, while others require you to specify parameters like a table name or a column name. As a result, each meta-query is provided as a meta-query template, containing {parameter_name} place holders for any information that you need to supply at runtime.
Figure 3 Meta-Query Selector
The dialog (showing a SQL Server-specific meta-query) presents queries available for the current database type in a tree on the left. Selecting one generates input fields required by that query on the right.
Once you open the Meta-Query Selector, you can drill down through the query tree on the left to find the meta-query of interest. Say, for example, you wanted to find all tables that contain a column named IdCustomer (see Figure 3). When you select a meta-query, all the place holders in its template are mapped to input fields, ready for you to enter values. Most queries are available for each database type, but they may generate different input fields; for example, Oracle includes the concept of a schema owner, while SQL Server includes the notion of multiple database names. Thus the database field in a SQL Server meta-query (figure 3 again) is omitted from the equivalent Oracle meta-query.
You can fill in as much (or as little) information as you wish, as an unfilled field implicitly matches all possible values. For the fields you do fill in, you can either use an exact term, or broaden your term with all the usual SQL wildcards.
Manipulating a Meta-Query
When you press ‘Execute’, your values replace the place-holders in the template and, if you’ve wired up the meta-query selector using the RevealQuery property as I have, you won’t even see the query. Your code will simply execute it, in keeping with the design of meta-queries as extensions to your application – you normally just want the application to do it, and you don’t care about how it got it’s results… but sometimes you do! So unlike regular menu commands, QueryPicker gives you the option to see – and manipulate – the contents of a meta-query: simply press the Shift key while clicking ‘Execute’. This toggles the RevealQuery property that your code should be checking, and the query is shown to the user.
If you do choose to reveal a query, the QueryPicker control gives you a choice of two representations. The default representation suppresses universal matches, and the alternate representation reveals them. A universal match corresponds to a field that you’ve left blank in the meta-query selector, and it can be specified implicitly (say nothing about the field in the WHERE predicate), or explicitly (specify that the field should return all values with an expression of the form fieldname LIKE “%”).
Returning one last time to the SQL Server meta-query in Figure 3, the default representation with universal matches suppressed is:
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SELECT ist.table_catalog AS Db, ist.table_schema AS 'Schema', so.name AS TableName, sc.name AS ColumnName, sc.colstat AS IsIdentity FROM syscolumns sc JOIN sysobjects so ON sc.id = so.id JOIN INFORMATION_SCHEMA.TABLES ist ON ist.table_name = so.name WHERE ist.table_catalog LIKE 'Customers' AND sc.name LIKE 'IdCustomer%' AND so.xtype IN ('U','V') ORDER BY so.name, sc.name |
The alternate representation, with universal matches revealed (and highlighted), is:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
SELECT ist.table_catalog AS Db, ist.table_schema AS 'Schema', so.name AS TableName, sc.name AS ColumnName, sc.colstat AS IsIdentity FROM syscolumns sc JOIN sysobjects so ON sc.id = so.id JOIN INFORMATION_SCHEMA.TABLES ist ON ist.table_name = so.name WHERE ist.table_catalog LIKE 'Customer' AND ist.table_schema LIKE '%' AND so.name LIKE '%' AND sc.name LIKE 'IdCustomer%' AND sc.colstat LIKE '%' AND so.xtype IN ('U','V') ORDER BY so.name, sc.name |
Whether or not you want to include the universal matches depends on your working style. Some people prefer filling out forms, while others would rather edit the text of a query directly. For the latter approach, once you select a query, just leave all the fields blank, press Shift (to display the query) and Alt (to reveal universal matches), and hit Execute. This gives you a query with every input field in the WHERE predicate formatted as fieldname LIKE “%“, and you’re now free to modify any of them.
The Meta-Query Library
Table 1 lists the meta-queries supplied with the application, and which database type each is currently available for. While you can’t see it from the table, they immediately allow you to do some useful searches, and this is especially true once you understand the queries and start writing your own templates. For example, the List columns/brief meta-query includes an IsIdentity? input field that lets you find all identity columns in all tables in a SQL Server database. When writing templates. it’s worth bearing in mind that some of these meta-queries are extremely short, and others are deceptively long. For example, the Show table schema query, in MySQL, is just four words, while in SQL Server it is a query exceeding 250 lines!
Category |
Description |
SQL Server |
Oracle |
MySql |
System |
Display DB version |
yes |
yes |
yes |
List databases |
yes |
N/A |
yes |
|
Constraints |
yes |
|||
Indexes |
yes |
|||
Primary keys |
yes |
yes |
||
Map primary/foreign keys |
yes |
|||
Procedures and functions |
yes |
yes |
||
International Characteristics |
yes |
|||
Session information |
yes |
|||
Table |
Row/column counts |
yes |
||
Non-empty tables |
yes |
yes1 |
||
Space utilization |
yes |
|||
Object level details |
yes |
|||
Copy a table |
yes |
yes |
yes |
|
Column |
Column info/brief |
yes |
yes |
yes |
Column info/details |
yes |
yes |
yes |
|
Data |
Seeds, row counts, and maxima |
yes |
||
DDL |
Show table schema |
yes2 |
yes |
yes |
Table 1. Standard Meta-Queries
1. Accuracy depends on when and how often Oracle gathers statistics.
2. SS2005 or later; limited to 8000 characters per element.
Template Structure
Now I’ll cover how to modify the templates themselves, and add new ones to your library. Below you see an original template before data is applied, with five place holders (highlighted for clarity). Each place holder appears in a predicate with the like operator rather than the equality operator, as this provides flexibility when you supply input values, and thus improves usability.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
SELECT ist.table_catalog AS Db, ist.table_schema AS 'Schema', so.name AS TableName, sc.name AS ColumnName, sc.colstat AS IsIdentity FROM syscolumns sc JOIN sysobjects so ON sc.id = so.id JOIN INFORMATION_SCHEMA.TABLES ist ON ist.table_name = so.name WHERE ist.table_catalog LIKE '{Database}' AND ist.table_schema LIKE '{Schema}' AND so.name LIKE '{Table}' AND sc.name LIKE '{Column}' AND sc.colstat LIKE '{IsIdentity?True/False/Either}' AND so.xtype IN ('U','V') ORDER BY so.name, sc.name; |
Each term surrounded by braces defines a place holder, and each unique place holder drives the generation of one input field in the meta-query selector when you select a query. You may have noticed that the template above is what’s selected in figure 3 (it just keeps coming back), and that each input line in that example derives from the place holders specified here. By default, these manifest as a single-line text box, but the final one, IsIdentity?, maps to a set of mutually exclusive radio buttons representing a Boolean state. Though you could use a generalized text box to type a 1 or a 0 for a Boolean field, it makes more sense to let the system handle this for you. The question mark in the place holder drives this distinction: any place holder name ending in a question mark generates a similar set of three buttons, and the place holder name-and the value displayed as the field name on the meta-query selector-includes the question mark. The remaining optional text specifies the labels of the three buttons, indicating a true value, a false value, and either value, in that order. If omitted, the defaults of Yes/No/Any are used, and you may specify one, two, or all three of these labels in your placeholder. For example, {IsIdentity?True/False} would generate labels of True, False, and Any.
Place holders do not have to be unique in the template. If the query template had a line like this:
1 2 |
WHERE {MyFieldName} IS NULL OR {MyFieldName} LIKE "{ProductId}" |
… then MyFieldName would appear as an input field just once, and the value you entered in the form would be applied to all occurrences of the {MyFieldName} place holder. (QA and SSMS also support templates with duplicate fields and default values; if you have two place holders with the same name and different defaults, they are considered identical – but only the default from the first occurrence is used.)
The only thing wrong with the query template above is that it is not instrumented for universal match suppression. So, here is the template in its final, instrumented form. Each place holder is placed within a suppression region-a portion of the text surrounded with double square brackets. If a place holder located within the boundaries of the suppression region receives no input (i.e. you leave the field blank on the meta-query selector) then the entire suppression region is completely omitted from the final query.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
SELECT ist.table_catalog AS Db, ist.table_schema AS 'Schema', so.name AS TableName, sc.name AS ColumnName, sc.colstat AS IsIdentity FROM syscolumns sc JOIN sysobjects so ON sc.id = so.id JOIN INFORMATION_SCHEMA.TABLES ist ON ist.table_name = so.name WHERE [[ist.table_catalog LIKE '{Database}' AND ]][[ist.table_schema LIKE '{Schema}' AND ]][[so.name LIKE '{Table}' AND ]][[sc.name LIKE '{Column}' AND ]][[sc.colstat LIKE '{IsIdentity?True/False/Either}' AND ]]so.xtype IN ('U','V') ORDER BY so.name, sc.name; |
The boundaries of the suppression region are important for two reasons. First, the brackets must be placed so that if either the brackets or the brackets and their contents are removed, the remaining text is still syntactically valid. Thus, for the {Database} place holder, the suppression region is:
1 |
ist.table_catalog LIKE '{Database}' AND |
…rather than just:
1 |
ist.table_catalog LIKE '{Database}' |
Second, the boundaries of the suppression region should be placed so that, cosmetically, the query has consistent white space whether the brackets are removed by themselves or removed with their contents. The template above follows this pattern (I’ll ignore line breaks for simplicity):
No space after the opening bracket, and a single space just before the end bracket ensure there are always single spaces between all words, whether any (or all) of the suppression regions are suppressed or not. If not suppressed, just the brackets are removed, leaving single-spaced text. With the suppression region removed, on the other hand, the line is still single-spaced:
preceding-text following-text
It’s also worth noticing that the WHERE predicate in the template query is composed of six clauses, but only the first five are in suppression regions. If all five clauses are suppressed, you leave the predicate with a single, syntactically valid clause: WHERE so.xtype in (‘U’,’V’). If all the clauses were in suppression regions and then removed, you would have a syntactically invalid WHERE. Since a query template should be robust, you need to make sure it handles the no-input case as well. A simple technique for this, borrowed from the toolbox of SQL injection, is to add a conjunction with an always-true clause, e.g.:
1 2 3 4 |
WHERE [[isr.routine_catalog LIKE '{Database}' AND ]][[isr.routine_schema LIKE '{Schema}' AND ]][[isr.routine_name LIKE '{Procedure/function}' AND ]]1=1 |
Template Storage in the Library File
Each query template is stored in the default query library – an XML data dictionary. A complete dictionary entry specifies not just the text of the query but also: the database type, the query type (which is just a display name for the query), a category (indicating where to place this query in the query tree Selector) and an optional tool tip (which appears when you hover over the query in the query tree). Here is the complete entry for the Oracle-variant of the current query:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
<Query> <DbType>Oracle</DbType> <Category>Column</Category> <QueryType>Column info/brief</QueryType> <ToolTip>Shows just table and column names for matching columns</ToolTip> <QueryText> SELECT table_name AS TableName, column_name AS ColumnName FROM sys.ALL_TAB_COLS WHERE [[owner LIKE '{Owner}' AND ]][[table_name LIKE '{Table}' AND ]][[column_name LIKE '{Column}' AND ]]NOT regexp_like(table_name, '[$_]', 'i') ORDER BY table_name, column_name </QueryText> </Query> |
The default query library is embedded in the DLL containing the QueryPicker, and the first time you open the meta-query selector, the code creates the query file (QueryLibrary.xml) by unwrapping the embedded resource. Your parent application stores this and other externalized resources in a subdirectory of its Application Data.
Customizing Meta-Queries
To modify any of the existing meta-queries, you’ll need to open the query library file (…\Application Data\<your-application-name>\QueryLibrary.xml) in a text or XML editor. The file consists of an unordered array of <Query> elements, each of which must contain a <DbType>, a <Category>, a <QueryType>, an optional <ToolTip>, and a <QueryText>. The <Query> elements may appear in any order in the file, although they are organized in the query tree of the Meta-Query Selector first by filtering on the current <DbType>, then grouping and sorting by <Category> as the first level of nodes in the query tree. As you add new <Query> elements, you need only conform to the simple schema in Figure 4, and you should be consistent with <Category> and <QueryType> values throughout the file.

Figure 4 Meta-Query Schema
The XML Schema for the query template library file consists of a simple array of queries, where each query contains 4 or 5 elements. The notations describe how these elements are used.
If you wish to edit the query library file in an XML-editor that provides guidance based on the schema, the schema file (QueryLibrary.xsd) is included in the download package.
Meta-Query Usage Summary
As a quick reference, the summary below makes it fast and easy to find the key details about using meta-queries and meta-query templates.
Item |
Value |
|
Place holder |
Generate a text box |
{field_name} |
Generate Boolean choices with default labels |
{field_name?} |
|
Generate Boolean choices with custom labels |
{field_name?yes_label/no_label/any_label} |
|
Require input for a field |
{field_name!} |
|
Universal match suppression region> |
[[ any SQL text containing a place holder ] |
|
Execution |
As a silent command |
Execute |
Display query without universal matches |
Shift-Execute |
|
Display query and reveal universal matches |
Alt-Shift-Execute |
|
Input Wildcards |
Percent ( % ) |
Any string of 0 or more characters |
Underscore ( _ ) |
Any single character |
|
[characters] or [character-character] |
Any single character within the enumerated set or the specified range, respectively |
|
[^characters] or [^character-character] |
Any single character not within the set or range, respectively |
Table 2 Meta-Query Usage Summary
Table 3 summarizes the features available in the QueryPicker control vs. the database-level tools described earlier. The green cells indicate advantages of the particular tool, and the QueryPicker lacks only one feature that QA or SSMS offers – default values. But then again, SSMS & QA don’t handle default values as robustly as they could, as discussed earlier.
# |
Characteristic |
QueryPicker |
SQL Server |
Oracle |
MySQL |
QA or SSMS |
SqlDeveloper |
SQLyog |
|||
1 |
Place holders |
yes |
yes |
N/A |
yes |
2 |
Active place holders |
yes |
yes |
N/A |
no |
3 |
Boolean place holders |
yes |
no |
N/A |
no |
4 |
Required place holders |
yes |
no |
N/A |
no |
5 |
Default values |
no |
yes |
N/A |
no |
6 |
Previous values |
yes |
no |
N/A |
no |
7 |
Handles multiple instances of one place holder |
yes |
mostly |
N/A |
no |
8 |
Optional universal match suppression |
yes |
no |
N/A |
no |
9 |
Optional query hiding |
yes |
no |
N/A |
no |
10 |
Optional auto-execution |
yes |
no |
N/A |
no |
11 |
Extensible template list |
yes |
yes |
N/A |
? |
12 |
Template list format |
tree |
tree |
N/A |
flat |
13 |
Shows template description |
yes |
No |
N/A |
no |
14 |
Parameter preview |
yes |
no |
N/A |
no |
15 |
Template preview |
no |
no |
N/A |
yes |
Table 3. QueryPicker vs. Templates in Standard DB Tools
The parenthetical items require support (albeit minor) from the parent application.
Rows 9 and 10 are parenthetical since they are not directly implemented by the QueryPicker: Row 9 requires your parent application to honor the RevealQuery property, and Row 10 requires your application to act on the query delivered to it. You will see in my next article how other building blocks enable you to easily provide that functionality.
The Process Flow
In wrapping up, here’s a quick recap of the process flow for using QueryPicker:
The act of opening the meta-query selector loads it with the set of meta-query templates germane to the current database type, which is identified through a Connection Editor (another control in my open source library). Ultimately the Connection Editor is just used to identify the database type, and you can do that in whatever way integrates best in your application. Once you select a specific meta-query from the query tree, the template for that meta-query is scanned for place holders, each of which generates an input field on the Selector dialog (either a text box or a set of radio buttons). You then fill out any or all of the input fields provided, and Execute. Your values are plugged into the template to produce a completed query, which is then either displayed in an editor, or sent off to the current database, or both. The figure below illustrates the steps from the meta-query selector and the query template library to a finished query instance.
Figure 5. Meta-Query Processing
The database type of your current connection (1) determines the available set of meta-queries when you open the meta-query selector (2). Choosing a meta-query accesses the query template (3) and generates input fields in the meta-query selector(4). Your entered values fill the place holders in the query template to produce a completed query (5).
I’ve given a fairly high-level description of what QueryPicker does, and why it exists. Fundamentally, I designed it because I was interested in meta-data, because anyone who’s mucking around with databases has a responsibility to understand the environment they’re tinkering with, and because I thought that task should be as easy as possible, regardless of what database types you’re working with. Sure, it’s a fairly niche tool, but I’ve designed it to be highly usable and extensible, so you can expand its capabilities as much as you like. In my next article, I’ll go into more low-level detail in terms of how QueryPicker works, and how you can wrap it all up and get it working. After that, I’ll tell you a little more about how the QueryPicker works.
Load comments