创建自定义模板 Building Custom Templates

** December 23, 2002
Using and Building Query Analyzer Templates **
** By Gregory A. Larsen **

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 = > '

  1<disk_name,varchar(300),c:\mssql\backup\><db_name,varchar(128),dba>_<version,varvhar(100),adhoc>.bak'  
  2&gt;   
  3&gt; 
  4---  
  5  
  6This 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. 
  7
  8Now 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. 
  9
 10When 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." 
 11
 12  
 13
 14
 15![](http://www.databasejournal.com/img/qa08.jpg)
 16
 17  
 18
 19
 20Next 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. 
 21
 22  
 23
 24
 25![](http://www.databasejournal.com/img/qa09.jpg)
 26
 27  
 28
 29
 30Now 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: 
 31
 32&gt; 
 33&gt;     -- =============================================
 34&gt;     -- Basic Create Database Template
 35&gt;     -- =============================================
 36&gt;     IF EXISTS (SELECT * 
 37&gt;     	       FROM   master..sysdatabases 
 38&gt;     	       WHERE  name = N'Demo_DB')
 39&gt;        DROP DATABASE Demo_DB
 40&gt;     GO
 41&gt;     
 42&gt;     CREATE DATABASE Demo_DB
 43&gt;     GO
 44&gt;     
 45&gt;     -- =============================================
 46&gt;     -- Create table basic template
 47&gt;     -- =============================================
 48&gt;     IF EXISTS(SELECT name 
 49&gt;     	      FROM 	 sysobjects 
 50&gt;     	      WHERE  name = N'MyTable' 
 51&gt;     	      AND 	 type = 'U')
 52&gt;        DROP TABLE MyTable
 53&gt;     GO
 54&gt;     
 55&gt;     CREATE TABLE MyTable (
 56&gt;     ID int NULL, 
 57&gt;     Description varchar(50) NOT NULL)
 58&gt;     GO
 59&gt;     
 60&gt;     -- =========================================================
 61&gt;     -- Backup database to disk file
 62&gt;     -- =========================================================
 63&gt;     backup database <db_name,varchar(128),dba>
 64&gt;       to disk = 
 65&gt;        '<disk_name,varchar(300),c:\mssql\backup\><db_name,varchar(128),dba>_&lt;&gt;version,varvhar(100),ADHOC&gt;.bak'  
 66&gt;   
 67&gt; 
 68---  
 69  
 70Now 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: 
 71
 72&gt; 
 73&gt;     -- =============================================
 74&gt;     -- Basic Create Database Template
 75&gt;     -- =============================================
 76&gt;     IF EXISTS (SELECT * 
 77&gt;     	       FROM   master..sysdatabases 
 78&gt;     	       WHERE  name = N'Demo_DB')
 79&gt;        DROP DATABASE Demo_DB
 80&gt;     GO
 81&gt;     
 82&gt;     CREATE DATABASE Demo_DB
 83&gt;     GO
 84&gt;     
 85&gt;     -- =============================================
 86&gt;     -- Create table basic template
 87&gt;     -- =============================================
 88&gt;     IF EXISTS(SELECT name 
 89&gt;     	      FROM 	 sysobjects 
 90&gt;     	      WHERE  name = N'MyTable' 
 91&gt;     	      AND 	 type = 'U')
 92&gt;        DROP TABLE MyTable
 93&gt;     GO
 94&gt;     
 95&gt;     CREATE TABLE MyTable (
 96&gt;     ID int NULL, 
 97&gt;     Description varchar(50) NOT NULL)
 98&gt;     GO
 99&gt;     
100&gt;     -- =========================================================
101&gt;     -- Backup database to disk file
102&gt;     -- =========================================================
103&gt;     backup database Demo_DB
104&gt;       to disk = 
105&gt;        'C:\mssql\backup\Demo_DB_ADHOC.bak'  
106&gt;   
107&gt; 
108---  
109  
110  
111
112
113###  Conclusion 
114
115As 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.</db_name,varchar(128),dba></disk_name,varchar(300),c:\mssql\backup\></db_name,varchar(128),dba></version,varvhar(100),adhoc></db_name,varchar(128),dba></disk_name,varchar(300),c:\mssql\backup\>