Templates are a great addition to any DBA’s toolkit. I’ll walk you through an overview and even throw in some free code – you’ll be more productive by Friday even if today is Friday.
First, thanks for stopping by!
Repetition again? Ugh…
I find that as a DBA, there are several things that are just repetitious. I’ve found a few tricks and tools to speed up my work and remove some of the drudgery. Today, I’ll talk about templates, and cover some of the others soon.
Templates aren’t new in SQL Server 2005 – they’re also available in 2000, and I won’t pretend to know or care about SQL Server 7.0 and previous versions – that’s someone else’s blog! I’m not knocking them, but I’m not using them either.
The strength of templates is that they shorten repetitive but slightly different tasks. One I use frequently, and display at the end of this post, helps me check index usage on a given table. I like to snoop around my indexes occasionally, checking on different tables when mood or suspicion strikes me. Today I may be concerned about dbo.customers, tomorrow it might be sales.orders. Furthermore, I might be investigating problems in the Demo database one minute, Beta the next, and Production when usage peaks after lunch.
I might even find <gasp!> that indexes are different in different databases. (And that might be okay…)
Sure, I could type out most of the code below, but why when templates will for me? Plus, I get quick, consistent answers when management is snooping around me.
So how do you get to these, you ask? I illustrate with SQL Server 2005 Express Management Studio (SSMS) since that’s what’s on my laptop and mention any obvious differences on my work PC.
What and where?
There’s no special file extension for templates – they’re straight SQL text. Templates located in the expected directory behave like templates in Excel, Word and many other tools – opening one, changing and saving triggers a Save As… dialog. This protects the original template file by prompting you to save somewhere else. Where is this expected directory, you ask? Yours will vary on OS, version, install drive, and username, but on my laptop (Vista, SQL Express):
C:\Users\Will\AppData\Roaming\Microsoft\Microsoft SQL Server\90\Tools\ShellSEM\Templates\Sql
My work desktop (WinXP, SQL2005 Standard):
C:\Documents and Settings\wcarter\Application Data\Microsoft\Microsoft SQL Server\90\Tools\Shell\Templates\Sql
Up one directory and you’ll find templates for other installed tools, such as SQL Mobile, Analysis Services, etc. Play with those, blog on them, comment below, or whatever…
To display a list of available templates, the short cut is Ctrl+Alt+T. (View / Template Explorer if you prefer menus). You get a file explorer style dockable window with templates grouped in folders. Right click any one for options like New, Edit, Search, Rename, and Delete (Edit is the way to change the template rather than a single instance of it). You can even add your own folders to stay organized.
Filling in the <Blanks, , >
The power is in the parameters! They take the form of
<Parameter name, Type, Default value>
Put one wherever you have dynamic code. You don’t even have to supply all the values – <P1, , > will be sufficient.
To replace them with specific values, Ctrl+Shift+M (Query / Specify Values for Template Parameters) brings up the Specify Values for Template Parameters window, listing each parameter’s:
· name
· type
· a textbox where you can enter a value. (If you specified a default, it’s already there.)
Especially for templates I don’t frequently use or those I share, I often “name” my parameters with a short phrase or question, like this:
<Table name?, sysname, customers>
<Audit date from:, datetime, dateadd(day, -1, getdate())>
<Audit date to:, datetime, getdate()>
Anything else?
Templates are also great for those occasional, moderately complex operations. Build some templates to handle those customer account credits that can’t be done in your app’s UI; quarterly data archival and purge processes; sales force hierarchy changes; and so on. You won’t have to remember where the file is or if you changed all instances of customer id, sales person name, date, etc; it’s just Ctrl+Alt+T, Ctrl+Shift+M away.
Of course, if you spend much time creating and customizing, find that directory above and back it up. I even keep copies on my thumb drive for when I’m visiting someone else’s cube…
The Point?
Templates are readily accessible, customizable, and time-saving. They make a great addition to any DBA’s toolkit. Check them out – you’ll be more productive by Friday.
They’ve saved me a lot of keystrokes (so I had some left for this blog!)
Again, thanks for stopping by!
The Promised Example – Enjoy!
/**********************************************************************************************************************
TEMPLATE: Snoop Indexes.sql
Finds usage stats for all indexes for a given table
Author: WillSQL4Food
**********************************************************************************************************************/
use <db, sysname, WillSQL4Food>
-- Index usage (seeks, scans, updates, etc.)
select object_name(i.object_id) table_name, i.[name] index_name
, c.[name] column_name, ic.key_ordinal, ic.is_included_column
, i.type_desc index_type, i.is_primary_key, i.is_unique, i.is_unique_constraint
, s.user_seeks, s.user_scans, s.user_lookups, s.user_updates
, s.last_user_seek, s.last_user_scan, s.last_user_lookup, s.last_user_update
from sys.indexes i
join sys.index_columns ic
on i.object_id = ic.object_id
and i.index_id = ic.index_id
join sys.columns c
on c.object_id = ic.object_id
and c.column_id = ic.column_id
left join sys.dm_db_index_usage_stats s
on i.object_id = s.object_id
and i.index_id = s.index_id
and s.database_id = db_id('<db, sysname, WillSQL4Food>')
where object_name(i.object_id) = '<tablename, sysname, >'
order by object_name(i.object_id), i.is_primary_key desc, index_name, ic.is_included_column, ic.key_ordinal, c.[name]
-- Fragmentation
select * from sys.dm_db_index_physical_stats(db_id('<db, sysname, WillSQL4Food>'), object_id('<tablename, sysname, >'), null, null, null)
-- Statistics generation
select * from sys.stats s where s.[object_id] = object_id('<tablename, sysname, >')
