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 3

By Gregory A. Larsen


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




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


















Thanks for your registration, follow us on our social networks to keep up-to-date