Introduction
The Query Analyzer (QA) in SQL Server 2000 is greatly enhanced over the QA in SQL Server 7.0. One of the enhancements, QA templates, can increase your productivity in developing code.
QA comes with over fifty canned
templates that you can use. Although these templates are useful as they are
installed, SQL Server exposes these templates for your modifications. Not only
can you modify existing templates but you can add your own. This article will
show you how to use and write your own Query Analyzer templates.
Using Templates
To show you how to display
and use the templates that come with SQL Server, let me go through a few
examples of using the templates to build a TSQL script. The first thing I will
show you is how to use the QA “Object Browser.”
The Object Browser is used to display the available QA templates. The Object Browser window can be
brought up by clicking on the Object Browser toolbar icon, hitting F8, or using
the Object Browser option under the QA “Tools” menu. When you bring up the
Object Browser window, it should look something like this:
The Object Brower window is
the Left Pane of this display. At the bottom of the Object Browser window you
will notice a Template tab. When you click on the Template tab, a list of
template directories will be displayed, like so:
QA comes with a set of
standard templates. You can modify the standard set as well as build your own
homegrown templates. I’ve created a number of my own templates for doing
routine database work Later on in the article I will show you how to create a
homegrown template. For now we’ll focus on how the templates work.
To show you how to use the
templates I am going to build a TSQL script using the standard supplied
templates and a custom built template. The TSQL script I am going to build
will create a database called DEMO_DB, define a table in the database and then
perform a database backup. Not too much — just enough to show you the
functionality of QA templates.
To start building this
script I will first need to open a blank QA window, and make sure the Object
Browser pane is open, as shown above. Next I click on the Template tab to
show the templates directories. After this, I expand the Create Database
folder by clicking on the “+” sign. When this is done a number of different
templates will be displayed to choose from. For my script I will select the
Create Database Basic Template. To bring the template into an empty QA
window I double click on the Create Database Basic Template. Double clicking
opens up a new QA window and inserts the create database template code into this
new window. My new window looks like this:
