I don’t currently do a lot of whiz-bang magic in my current job role. The majority of my day consists of writing reports, which can be boiled down to any of the follow:
- Writing a stored procedure to generate the report data
- Creating the report layout in a WYSIWYG-style editor.
- Writing SQL script to deploy those changes to our customers.
The most exciting part is generally when I need to roll up my sleeves and figure out something fancy for the stored procedure. Much more often though, I’m hammering out INSERT scripts or UPDATE scripts with some XML stored as VARCHAR(MAX).
Because of the robotic nature of some of this, I decided to start using some code snippets. I couldn’t find any snippet feature in SQL Sever Management Studio, so I discovered SSMS Tools Pack.
This add-on has already saved me an enormous amount of time. I’ve only used a few of the features so far. SQL code snippets have obviously saved me a lot of time, but I found some other uses that I didn’t expect. I often need to test a report across multiple databases. With the tool pack, I can right click on a script, click “Run on multiple targets” and be prompted with a list of text boxes for each database I want to run it against. There’s nothing particularly magical about this process – it just creates a new tab and separates copies of the script with ‘USE [DatabaseName]’ lines, but it saves me a reasonable amount of time.
If you spend much time in SSMS, I recommend giving it a whirl. The full list of features:
- Run one script on multiple databases
- Copy execution plan bitmaps to clipboard or file
- Search results in Grid Mode or Execution Plans
- Generate Insert statements from resultsets, tables, or database
- Regions and Debug sections
- Running custom scripts from Object Explorer
- CRUD stored procedure generation
- New query template