Experiencing the power of SQL templates

by Arthur Fuller | Apr 04, 2005 7:12:00 PM

Tags: Databases, SQL, Arthur Fuller, QA

Takeaway: SQL Server ships with a variety of templates designed to simplify the most common tasks. Arthur Fuller explains how you can customize these templates, or create new folders in the SQL Query Analyzer folder and populate them with your own templates.

TechRepublic's free SQL Server newsletter, delivered each Tuesday, contains hands-on tips that will help you become more adept with this powerful relational database management system. Automatically subscribe today!

SQL Server 2000 Query Analyzer (QA) has a library of templates that you can use to create stored procedures. The QA interface tends to hide this feature, which may be why so few developers know it's there.

When you open QA and then open its object browser, you’ll see two tabs at the bottom of the browser. Click the Templates tab, and you’ll see a list of the available templates. Double-click any template, and QA will open a new query window; then, paste the template into it.

SQL Server ships with a variety of templates designed to simplify the most common tasks. You’ll find them in the Program Files\Microsoft SQL Server\80\Tools\Templates\SQL Query Analyzer folder, which contains 13 subfolders that house templates devoted to a particular task (such as Create Table). Each folder contains several templates; here's one of the Create Table templates:

-- =============================================

-- Basic Create Database Template

-- =============================================

IF EXISTS (SELECT * 

....   FROM   master..sysdatabases

....   WHERE  name = N'<database_name, sysname, test_db>')

....DROP DATABASE <database_name, sysname, test_db>

GO



CREATE DATABASE <database_name, sysname, test_db>

GO

The angle brackets in the code denote replaceable markers. The Replace Template Parameters command on the QA Edit menu invokes a dialog box that lets you replace all the parameters at one time. Note: The non-intuitive shortcut to open this dialog box is [Ctrl][Shift]M.

What may not be apparent is that you can combine several templates into one stored procedure. Once your first template is in its edit window, just drag any other template into the same window.

Many organizations have standards that govern the style, indentation, and header of their stored procedures. This is where you can experience the real power of templates. You can customize existing templates simply by loading them into your favorite text editor. Even better, you can create new folders in the SQL Query Analyzer folder and populate them with your own templates. They'll show up automatically in the QA object browser. There are no templates for actions such as DELETE or UPDATE; however, you can roll your own templates for such actions and add them to the SQL Query Analyzer folder.

If you work in a multi-developer environment, you can tell QA to look elsewhere (such as a shared folder on the network) for its templates. From the QA menu, select Tools | Options and then edit the template directory setting.

If you have yet to explore these templates, I encourage you to do so. You’ll find that you save a lot of typing, and, with a little customization, you can adhere automatically to your organization’s standards.

5 comment(s)