Templates in SQL Server

I asked a few of my colleagues whether they knew that there were templates in SQL Server. Their responses were typically either "Is it?" or "Yeah. Saw it once when I was trying to create a new query. So?"

When Microsoft tries to pack in so many features into SQL Server 2005 aimed at improving the life of developers, why haven't they done much on the templates, which I feel, is a powerful tool for speeding up the development process.

Maybe, Microsoft isn't too keen on having these templates in the first place:) The templates seemed to be hidden in Query Analyzer and looked more like an undocumented feature known to a very few folks (and I don't consider myself to be in one of them).

The following is an excerpt from BOL on templates:
Lets look at what we have in hand. The most important part of a template are the template parameters.

Let's take a small example.

I need to create a template to select all the rows from a table (accepted as input).
This is how I would go about doing creating the template.


SELECT * FROM <table_name, sysname, sample_table>

Here,
table_name is the template parameter
sysname is the type of the parameter
sample_table is the default value
Now, when you try to specify value for the template parameters (Ctrl + Shift + M), I get a pop-up asking for the table name for which the query has to be generated. Faily straightforward.

Now, what can I do if I want to select all the columns explicitely written (since using * is not a good practice)

With the current functionality, I would need to type all the column names.
But is there a possibility to automate it? I wish we had it already but I think its possible! Once I know the table name why do I have to type out all the columns? Anybody who had looked at the information_schema views can tell how it can be generated.

Here is how it can be generated in SQL Server 2005 for getting the column names as comma separated list for a random table in Northwind database.

USE NORTHWIND
DECLARE @TableName varchar(100)
set @TableName = 'EMPLOYEES'

SELECT CAST((select COLUMN_NAME + ',' from INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName FOR XML PATH('')) AS VARCHAR(MAX))

So, ideally speaking, if I have the table name, I have all the information to generate the query. This is how I would suggest the template parameter definition should look like:

<Parameter_Name, Type, default, Order_Of_Evaluation>

Here, the parameter_name and DataType will mean the same as what it means in the current template parameter definition. The default will be a valid query that will output a scalar value. The Order_of_Evaluation(OE) will define when the default should be evaluated. The query in the default should be able to access the other parameters which have the OE before its own OE.

So, I would write my template this way for getting all the columns of a table.

SELECT <@COLUMNVALUES, VARCHAR(MAX),
SELECT CAST((select COLUMN_NAME + ',' from INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TABLENAME FOR XML PATH('')) AS VARCHAR(MAX)),2>
FROM
<@TABLENAME, SYSNAME, Select 'sample_table',1>

Here only, the parameters having the OE as 1 will be entered by the user. The rest should be auto-generated. We can have a convention for creating parameter_name so that it can be substituted automatically in the default query for values evaluated at a higher OE.

The above is a simple example. We can go ahead and use all the schema information and automate simple joins and filter on primary keys, indexes.

This is just my idea. It would be great if it's integrated in SSMS with the template browser.
The templates once created can be shared and used by all.
The other enhancement, a minor one, that I would like to have is template inheritence (in-line substitution).
For example, while creating any database object we do the existence check and drop the object if it exists. It can be put in a seperate template and can be called in whichever template we create. Just before the parameter substitution, the template name should be substituted with the template text.
Is there a way to automate more, with the current templates, than what I have understood. Let me know your thoughts and comments.


"SQL Server Management Studio comes with templates for many common tasks, but the real power of templates lies in the ability to create a custom template for a complex script that you must create frequently. In this practice you will create a simple script with few parameters, but templates are useful for long, repetitive scripts, too."

Now, the ability to create custom templates is good. But, the level of automation that we are able to do with it - is that good enough? I don't think so. Right now, a template is useful in doing multiple find and replace from a single pop-up window. Is there anything more to it that I am missing???