As part of a SQL Manchester pass presentation I did called “The Proactive Database Professional” I showed a few things that could make you more productive. One was a customisable tools set, which I have detailed below
The first thing needed is a set of scripts, ( downloadable links at bottom of the blog from One Drive / Google Drive) these consist of the following procedures.
The names are quite descriptive in what they do, but as shown shortly you can add much more to these to make them useful tools
The code in the scripts are basically DMV( Dynamic Management views) and SQL that I have acquired/written over the years while being a developer / DBA / Data Architect.
One that should be on every production system is Adam Mechanics sp_WhoIsActive procedure, which is also part of the tools.
These consist of routines that can help you monitor / check for issues or can get you out of a fix. I’ll list / show some of them in future blogs in more detail. But putting them on a development system and having a play will show you more yourself.
For each Procedure ( these are created as procedures to make them easier to use / remember – I do have a standalone version for system that you can’t roll out procedures too) I add it in the format as sp_DBA_[Name of the procedure] the method in the madness is that you can then create an index of these useful procedures.
This is done by creating 3 extended properties as below
- Version This allows you to easily view versions on different servers and keep versions upto date
- Description – Says what it is on the tin, description of what the procedure does and how it can be used
- Parameters – Parameters that the procedure takes
Then using the code from sp_DBA_tools procedure it displays the tools available with description , version and parameters is can take
Additionally I make this a short cut key ( Tools / Option / Environment / Keyboard) – will need to open a new query window before it works
You will get something like the following by executing sp_DBA_tools
Rolling these out is easy you can roll out individually, Obviously we don’t want to do that as were being proactive database professionals right, so we can create a master roll out script. We can use SQLCMD – (Query \ SQLCmdmode in SSMS) below we specify file location (1), the best thing with this its on your local machine. So you can change to Server A or Server B and the script will still work as its looking at the same location
We then we specify the name of the SQL file which is the name of the procedure (2) and when executing it will run all the procedure in at a single time to that server. So literally its a 30 second job to roll these out onto a new server
I also have a set of PowerShell scripts that allow these to be pushed out to many servers at a single time. This will be shown in the next blog post.
Links to zip file