Create a custom template building custom templates

Create | Template December 23, 2002
Using and building Query Analyzer Templates
by Gregory A. Larsen



Building Custom Templates
By now you should is somewhat familiar with the using pre-existing templates to build your SQL code. It is now the look in how can leverage templates to streamline your administration. Existing templates, but can also modify the existing templates and build your own home grown temp Lates.

SQL Server comes with a set of "templates" you install SQL Server. The Microsoft supplied templates are stored (if used the default installation) in directory called "C:\Program Files \microsoft SQL Server\80\tools\templates QL Query analyzer\. " In this directory there are a number of subdirectories, one for each folder you are 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 Have a "tql" extension.

For example purposes I am going to create a new template to support creating a database backup. To ensure this I homegrown templates are stored separately from the standard ones, I'll build a new template folder to Place my custom templates. All templates for create must have a "TQL" extention in order to be recognized as a template.

Before I builds my database backup template, l create a new directory for all my homegrown templates called "My Templates." This new directory are created under the location where all standard Microsoft templates are stored. In my case, I'll create a new template directory called C:\Program Files\Microsoft SQL Server\80\tools\templates QL Quer Y analyzer\my Templates. " Now I am ready to build a template.

The template I am going to create are called "Database Backup to Disk file.tql." Whatever I Name The template is what would be displayed in the "template" pane in QA, minus the "TQL" extention. I'll use NOTEPAD to create the new template that would build a simple "BACKUP DATABASE" command. The template built would look like this:

--=========================================================--Backup database to disk file--======================= ==================================backup database to disk = ' <disk_name,varchar ', c:\mssql\backup\><db_ Name,varchar (128), Dba>_<version,varvhar (MB), Adhoc>.bak '



This template contains three different parameters. The The parameter is db_name and is used to identify the database, that would be backed up. As you can the, this parameter are 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'll 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 Stateme Nt. Now I am going to with my custom built ' database backup to Disk File ' template to add a ' database backup ' command to the EN D of my script.

When I display the QA "Template" pane, I now have a new folder called "My Templates." If for some reason I don ' t to the new template, I right click on the "Templates" folder and choose "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 I new template "Database Backup to Disk File" would 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 ' my QA script. After I drag and drop my template, the My script looks like this:

--=============================================--Basic Create Database template--================================ =============if EXISTS (SELECT * from Master. sysdatabases WHERE name = N ' demo_db ') DROP database demo_dbgocreate database demo_dbgo--=============================== ==============--Create table Basic template--=============================================if EXISTS (SELECT name from sysobjects WHERE name = N ' MyTable ' and type = ' U ') DROP table mytablegocreate table MyTable (ID int NULL, Description Varc Har () not NULL) go--=========================================================--Backup database to disk file--= = = = ===================================================backup database <db_name,varchar (128),DBA> to disk = ' < Disk_name,varchar, C:\mssql\backup\><db_name,varchar (128), Dba>_<>version,varvhar (MB), ADHOC >.bak '



Now, all, left to complete, my script are to replace the parameters. Once again I use the Replace Template Parameter menu to replace parameters. This time I is need to enter a value for the db_name parameter; I enter "demo_db." For the rest of the parameters I'll 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_dbgocreate database demo_dbgo--=============================== ==============--Create table Basic template--=============================================if EXISTS (SELECT name from sysobjects WHERE name = N ' MyTable ' and type = ' U ') DROP table mytablegocreate table MyTable (ID int NULL, Description Varc Har () 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, the templates are useful for building scripts. Templates can save you valuable time developing code. By building your own templates and customizing the existing templates, can streamline your process and Eli minate syntax errors. Next time you write some the code that might is used over and over again, consider using it as a base for building a QA Templa Te.