I have, as I would imagine all you guys do, a large collection of template scripts that make it easier for me to do certain tasks quickly.
I have several hundred but I imagine some people have thousands (poor you!) of scripts to store, maintain and require at a moment's notice.
I started to prefix the date to my scripts so I would have the datetime I created the script. This worked well until I was trying to remember when I wrote that particular script. Next I went to folders but now if I need a disk space script did I save it in the Maintenance, Tasks or Diligence folder?!!
How do you get round this?
asked Mar 20 '10 at 07:20 AM in Default
I use templates quite a bit for the generic scripts, like index stats, locks and blocks etc. That way I can get to them from whatever I am doing in SSMS if there is an emergency. I even wrote a blog article about it with the aim of it being the first of a few (http://jonathanallen69.blogspot.com/2010/01/writing-tsql-faster-with-templates.html) but haven't followed up with the next one yet.
Generally for database development work I create a SSMS solution with the name of the database and then create projects in that and store the individual scripts in there. They can be named as you like because their physical location describes what they are affecting.
[Edit:] Forgot to mention, get a good indexing tool to monitor the windows directories where you store stuff. Thay way you can just search for the keywords you want to find. I have bounced between Google Desktop Search, Copernic Search and Windows Search as our network team block them in rotation! If its a scrap I havent finished but I dont want to lose I name the file by date and activity and it goes in My Projects eg 20100320-NewTableForHealthAndSafety.sql
I use code snippets rather than templates, because my editor allows me to. I find them to be quite a lot quicker to work with, because they're instantly accessible from the query interface, and they tie in nicely with the way I work within visual studio. So if I want a create table snippet, I type ctbl then TAB and then just edit the table name. loop then TAB gives me a while loop. crsr then TAB gives me a cursor (I don't use that one often, but it saves a lot of typing!).
But, to be honest, I tend to keep the number of snippets / templates I have low, so that I don't have to worry about having a massive filing system.
answered Mar 20 '10 at 01:26 PM
Matt Whitfield ♦♦
I try and organise mine into meaningful folders (Creation scripts, Index Maintenance, Montioring etc.), but also try and give it a meaningful name such as 'Create new database.sql'.
But I'm with you - sometimes it takes me a moment of either thought or navigation to find out where I put that all-important script.
I find after a few months too, that the default folder ('My Projects' - or something like that) also has a number of 'work in progress' or 'just save this quickly' scripts - so I tend to go through, renaming and relocating those. One DBA I used to work with had a flat folder structure with thousands of files named 'How to xxxxx' - it said it helped him, but I could never find anything I wanted!
Just seen Fatherjacks answer - I think solutions may be a good way of doing this, I just never have got into this whole solution/project thing.
answered Mar 20 '10 at 07:40 AM
Kev Riley ♦♦
I find proper version control software helps, though it is no full solution. My company has Visual Source Safe at work, but that has the advantage of partially integrating with SSMS 2008. At home, I use mercurial (Hg). I know Red Gate is currently working on a product to help version control integrate better with SQL Server, and I am eager to give it a try.
Beyond that, I share the joy of nested levels of subfolders containing categorized scripts and variations on scripts.
answered Mar 20 '10 at 03:25 PM
It does get unmanageable.
I've had many of those "maintenance or backup?" moments when searching myself.
I try to keep a directory hierarchy with small branches at the top and sub-categorize. Like Kev I find a lot of Work In Progress that need to be (re-)evaluated and renamed/move.
I also have a lot of templates that contain numerous snippets grouped together so I only open one file and search through it. I use UltraEdit a lot so I just take advantage of the template feature for these - most editors/IDEs have similar functionality.
I'm not worried about 8.3 naming or corporate naming standards for templates so I tend to use long, descriptive names for my scripts. As they get pushed to PROD, or what have you, I give them an "approved" name and I keep a spreadsheet with the name mappings for my own sanity. (I support many Clients and standards range.)
But when you think it through, shouldn't we just use a database? I'm sure each of us has access to one or two :)
answered Mar 20 '10 at 01:26 PM