SQL templates

SSMS has a very handy feature in the Template Explorer (Ctrl-Alt-T) that contains 100's of templates for all sorts of scripting tasks you might require:

• Open the Template Explorer
• Drill-down to the category
• Right-click on the desired task and click Open.
• Press Ctrl-Shift-M to display a popup screen & enter your parameters
• Click OK

You can also create custom templates:

• Right-click on the top node, select New ~ Folder and give it a name e.g. Custom
• Right-click on the Custom folder, select New ~ Template and give it a name e.g. MyTemplate
• Right-click on MyTemplate, select Edit
• Observe the Connect dialog & connect to the your server
• Type in the template code and click Save

. . .but when you do that, the default location is something like

C:\Documents and Settings\Owner\Application Data\Microsoft\Microsoft SQL Server\90\Tools\Shell\Templates\Sql\Custom

so you will probably want to use Save As... and store your custom templates in a subfolder closer to where you keep your regular scripts & apply source control. NOTE: Your template file should also be set to read-only, because when you do the Ctrl-Shift-M the displayed file name does not change, so you want to protect your templates.

One basic example of using a template is to add a new column that has a default value.

Here is a sample hard-coded script:

ALTER TABLE dbo.Company
ADD Flag bit NOT NULL
CONSTRAINT DF__Company__Flag
DEFAULT (0)
GO

You could of course create a simple template where you just search / replace ttt = table name, ccc = column name, etc. but it gets very cumbersome:

ALTER TABLE dbo.ttt
ADD ccc ddd NOT NULL
CONSTRAINT DF__ttt__ccc
DEFAULT (vvv)
GO

This is a template that works with SSMS:

important note - blogger does not display less-than or greater-than symbols, so in this example you need to replace the { with the less-than symbol, and replace the } with the greater-than symbol

ALTER TABLE dbo.{table_name, sysname, table_name}
ADD {field_name, sysname, field_name} {data_type, nvarchar(128), data_type} {nullable, nvarchar, NOT NULL}
CONSTRAINT DF__{table_name, sysname, table_name}__{field_name, sysname, field_name}
DEFAULT ({default_value, nvarchar, default_value})
GO

Within each set of brackets, the 3 arguments are:

• title of the parameter that is displayed in the popup screen
• the data type of the parameter that is to be inserted
• the default value of the parameter that is to be inserted

I have posted a few templates as Word documents with the full code, screen shots & more examples, you can view them here:

column_add_with_default

column_drop_add_with_default

table_drop_create

Remote Queries in Access

MS-Access makes it very easy to link to tables in other Access databases . . .

File ~ Get External Data ~ Link Tables...

just navigate to your mdb file & click Link, select the table(s) and click OK.

But you can also connect & retrieve data from another file, without creating a linked table. This little-known technique has been around AFAIK since the Access 2.0 days. I'm not suggesting that you use this instead of linked tables, but it has certain advantages & flexibility in specific cases.

Begin a new query, don't add any tables, and switch to the SQL view. Type in your SQL statement, which might look like this:

SELECT *
FROM Table1
IN 'C:\MyFile.mdb'

Note the single quotes around the file name. When you run this query, you'll see the data in that external table. If you want to apply some criteria, you could use this:

SELECT *
FROM Table1
IN 'C:\MyFile.mdb'
WHERE MyField = "something"

One advantage of this technique is that it bypasses the show / hide settings for hidden & system objects ( Tools ~ Options ~ View ~ Hidden objects, and ~ System objects ).

Let's say you want to view all the user tables in an external file - you could use this:

SELECT [name]
FROM MSysObjects
IN 'C:\MyFile.mdb'
WHERE [type] = 1
AND LEFT([name],4) <> "MSys"

MSysObjects is a system table that contains all the objects in the Access mdb. The type = 1 denotes local (not linked) native Access tables.

Taking this a step farther (with a little bit of VBA code) you could view a list of the fields in that external table (watch for line breaks) . . .

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim fld As DAO.Field
Dim sql As String
'
Set db = CurrentDb
sql = "SELECT * FROM Table1 IN 'C:\MyFile.mdb' "
Set qdf = db.CreateQueryDef("", sql)
For Each fld In qdf.Fields
Debug.Print fld.Name
Next fld