Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Dec 23, 2002

Using and Building Query Analyzer Templates - Page 2

By Gregory A. Larsen


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




MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM