Using and Building Query Analyzer Templates

Monday Dec 23rd 2002 by Gregory A. Larsen
Share:

SQL Server 2000's Query Analyzer (QA) is greatly enhanced over SQL Server 7.0's QA and specifically offers an enhancement, QA templates, that can increase your productivity in developing code. This article from Gregory Larsen shows you how to use and write your own Query Analyzer templates.


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:




Page 2: Using Templates (Continued)



Notice that this TSQL code is only a shell of the create database command. This shell contains parameter definitions. Parameter definitions have the following format:

<parameter_name, data_type, value>

where parameter_name is the name of the parameter, data_type is the data type of the parameter, and value is the default value that is to be used to replace every occurrence of the parameter in the shell, provided an alternative is not suggested.

In the Create Database Basic Template there is only one parameter definition, and each occurrence of this parameter looks like "<database_name, sysname, test_db>." This parameter appears in three different places in this template.

Now I could manually change all the parameter options in the QA editor, although that would be slow. To quickly change the parameters there is a Replace Template Parameter menu. This menu can be brought up by clicking on the Replace Template Parameter option under the "Edit" menu, or by entering Ctrl+Shift+M. The Replace Template Parameter menu looks like this:



Note that this menu only displays one parameter. That is because the Database Create Basic Template only contains a single parameter. If there are multiple different parameters in a template, then all parameters would be displayed on this menu.

On the Replace Template Parameter menu you have three different columns. The Parameter column identifies the name of each parameter, the Type column shows what data type is associated with each parameter, and the Value parameter shows what value will be used as a default for each parameter. In this particular example there is one parameter database_name that is of type "sysname," which has a default value of "test_db." The Replace Template Parameter menu retrieves each of these parameters from the code in QA's query pane, whereever it finds a string in the pane that looks like a parameter statement (a "<" followed by a variable, followed by two commas, followed by a closing ">").

The Replace Template Parameter menu is used to replace all the parameters in the QA template. For each parameter this menu allows you to take the default value or enter a value to be used instead of the default. If you want to override the default, type over the default in the value column. Once you are done entering some, or all, of the parameters, you need to click on the "Replace All" button. At this point the template parameter strings will be replaced with the values you entered on the Replace Template Parameter menu.

Since for my example I didn't want to use the default for the "database_name," I typed over "test_db" and entered "DEMO_DB" into the Replace Template Parameter menu like so:



After I click on the "Replace All" button, my QA script looks like this:

-- =============================================
-- Basic Create Database Template
-- =============================================
IF EXISTS (SELECT *
           FROM   master..sysdatabases 
           WHERE  name = N'DEMO_DB')
           DROP DATABASE DEMO_DB
GO

CREATE DATABASE DEMO_DB
GO

Now it is time to add onto this script with another template.


Page 3: Adding Another Template to the Script



The next thing I need to do is create a table. I am going to create a table called MyDemo. Once again I am going to use the Object Browser to find a suitable Create Table template. I will use the Create Table Basic Template. Using the left mouse button, I click on the Create Table Basic Template, but I don't release the button. Instead, I drag the mouse pointer over to my QA window and position my pointer just below the last "GO" command that was created with the create database template, and then I release the mouse button.

This will place the template code in my QA window at the position of my mouse. Also, the code remains selected. Leaving the code selected can be useful, especially if my mouse happens to jump and the code gets inserted into the wrong position. By leaving it highlighted, a little pointer mistake can easily be undone with the delete button or by using the cut/paste functions.

Once I get the Create Table Basic Template located in my script in the appropriate spot, I click anywhere in the QA window to remove the highlight from the template code. Now my code should look something like this:

-- =============================================
-- Basic Create Database Template
-- =============================================
IF EXISTS (SELECT * 
	   FROM   master..sysdatabases 
	   WHERE  name = N'DEMO_DB')
	DROP DATABASE DEMO_DB
GO

CREATE DATABASE DEMO_DB
GO

-- =============================================
-- Create table basic template
-- =============================================
IF EXISTS(SELECT name 
	  FROM 	 sysobjects 
	  WHERE  name = N'' 
	  AND 	 type = 'U')
   DROP TABLE 
GO

CREATE TABLE  (
  NULL, 
  NOT NULL)
GO

Note that this template has five different parameters: table_name, column_1, datatype_for_column_1, column_2, and datatype_for_column_2. Once again I will use the Replace Template Parameters menu to set the replacement values for these parameters. But first l need to determine what values I would like for each of the parameters. Here are the values I plan to use:

table_name = 'MyDemo'
column_1 = 'ID'
datatype_for_column_1 = 'int'
column_2 = 'Description'
date type_for_column_2 = 'varchar(50)'

Now that I know what I would like for each template value, I bring up the Replace Template Parameters menu, which should look like this:



Notice that there are five different parameters to enter this time, and the menu is positioning my cursor on the first parameter (table_name). I enter "MyDemo" for a table name and then hit the down arrow to go to the next parameter. For column_1, I enter ID and then hit the down arrow. Now at this point I am on the datatype_for_column_1 field. Since the template has a default value for this column of "int," I have two choices. I can either enter "int," or I can just take the default by using the down arrow. I'm lazy, so the down arrow works for me. Now for column_2, I want to enter "Description" and then use the down arrow. For the datatype_for_column_2 value, I notice it defaults to "int" and I want a "varchar(50)," so I replace the default "int" value with "varchar(50)."

My Replace Template Parameter menu looks like this after I have entered a value for each parameter:



At this point I can hit ENTER, or I can click on the "Replace All" button. After doing one of these, my code in QA will look like this:

-- =============================================
-- Basic Create Database Template
-- =============================================
IF EXISTS (SELECT * 
	       FROM   master..sysdatabases 
	       WHERE  name = N'Demo_DB')
   DROP DATABASE Demo_DB
GO

CREATE DATABASE Demo_DB
GO

-- =============================================
-- Create table basic template
-- =============================================
IF EXISTS(SELECT name 
	      FROM 	 sysobjects 
	      WHERE  name = N'MyDemo' 
	      AND 	 type = 'U')
   DROP TABLE MyDemo
GO

CREATE TABLE MyDemo (
ID int NULL, 
Description varchar(50) NOT NULL)
GO


Page 4: Building Custom Templates



Building Custom Templates

By now you should be somewhat familiar with using pre-existing templates to build your SQL code. It is now time to look at how you can leverage templates to streamline your administration. Not only can you use existing templates, but you can also modify the existing templates and build your own home grown templates.

SQL Server comes with a set of templates when you install SQL Server. The Microsoft supplied templates are stored (if you used the default installation) in a directory called "C:\Program Files\Microsoft SQL Server\80\Tools\Templates\SQL Query Analyzer\." In this directory there are a number of subdirectories, one for each folder you see on the QA Template pane. If you didn't take the default when installing SQL Server, you can find the template directory by searching for files that have a "tql" extension.

For example purposes I am going to create a new template to support creating a database backup. To ensure that my homegrown templates are stored separately from the standard ones, I will build a new template folder to place my custom templates. All templates you create must have a "tql" extention in order to be recognized as a template.

Before I build my database backup template, l create a new directory for all my homegrown templates called "My Templates." This new directory is created under the location where all the standard Microsoft templates are stored. In my case, I will create a new template directory called C:\Program Files\Microsoft SQL Server\80\Tools\Templates\SQL Query Analyzer\My Templates." Now I am ready to build a template.

The template I am going to create is called "Database Backup To Disk File.tql." Whatever I name the template is what will be displayed in the "Template" pane in QA, minus the "tql" extention. I will use NOTEPAD to create this new template that will build a simple "BACKUP DATABASE" command. The template built will look like this:

-- =========================================================
-- Backup database to disk file
-- =========================================================
backup database 
  to disk = 
   '<disk_name,varchar(300),C:\mssql\backup\><db_name,varchar(128),dba>_<version,varvhar(100),ADHOC>.bak'

This template contains three different parameters. The first parameter is db_name and is used to identify the database that will be backed up. As you can see, this parameter is defined as a varchar(128), and defaults to "DBA." The second parameter is disk_name, which defaults to the standard place for database backups on my machine. The third parameter provides a way to specify the version name for the backup.

Now I will put the final touches on the script I am building. So far I have used two standard templates to create my script, which contains a create database and a create table statement. Now I am going to use my custom built "Database Backup To Disk File" template to add a "DATABASE BACKUP" command to the end of my script.

When I display the QA "Template" pane, I now see a new folder called "My Templates." If for some reason I don't see the new template, I right click on the "Templates" folder and choose the "Refresh" option. Notice below the newly added template folder "My Templates."



Next I click on the "+" sign next to the "My Template" folder to expand. In the expanded view my new template "Database Backup To Disk File" will be displayed, as in the screen shot below.



Now I click on the Database Backup To Disk File template, drag it to the QA pane, and drop it at the end of my QA script. After I drag and drop my template, my script looks like this:

-- =============================================
-- Basic Create Database Template
-- =============================================
IF EXISTS (SELECT * 
	       FROM   master..sysdatabases 
	       WHERE  name = N'Demo_DB')
   DROP DATABASE Demo_DB
GO

CREATE DATABASE Demo_DB
GO

-- =============================================
-- Create table basic template
-- =============================================
IF EXISTS(SELECT name 
	      FROM 	 sysobjects 
	      WHERE  name = N'MyTable' 
	      AND 	 type = 'U')
   DROP TABLE MyTable
GO

CREATE TABLE MyTable (
ID int NULL, 
Description varchar(50) NOT NULL)
GO

-- =========================================================
-- Backup database to disk file
-- =========================================================
backup database <db_name,varchar(128),DBA>
  to disk = 
   '<disk_name,varchar(300),C:\mssql\backup\><db_name,varchar(128),dba>_<>version,varvhar(100),ADHOC>.bak'

Now all that is left to complete my script is to replace the parameters. Once again I use the Replace Template Parameter menu to replace parameters. This time I only need to enter a value for the db_name parameter; I enter "Demo_DB." For the rest of the parameters I will take the default values. My final script look like this:

-- =============================================
-- Basic Create Database Template
-- =============================================
IF EXISTS (SELECT * 
	       FROM   master..sysdatabases 
	       WHERE  name = N'Demo_DB')
   DROP DATABASE Demo_DB
GO

CREATE DATABASE Demo_DB
GO

-- =============================================
-- Create table basic template
-- =============================================
IF EXISTS(SELECT name 
	      FROM 	 sysobjects 
	      WHERE  name = N'MyTable' 
	      AND 	 type = 'U')
   DROP TABLE MyTable
GO

CREATE TABLE MyTable (
ID int NULL, 
Description varchar(50) NOT NULL)
GO

-- =========================================================
-- Backup database to disk file
-- =========================================================
backup database Demo_DB
  to disk = 
   'C:\mssql\backup\Demo_DB_ADHOC.bak'

Conclusion

As you can see, templates are useful for building scripts. Templates can save you valuable time when developing code. By building your own templates and customizing the existing templates, you can streamline your development process and eliminate syntax errors. Next time you write some code that might be used over and over again, consider using it as a base for building a QA template.

Gregory A. Larsen, gregalarsen@msn.com


See All Articles by Columnist Gregory Larsen


Share:
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved