TSQL Tuesday
Woohoo, it is an illuminating time of the month this second Tuesday of February 2020 and that also means it is time for another group blog party called TSQLTuesday. This party that was started by Adam Machanic has now been going for long enough that changes have happened (such as Steve Jones (b | t) managing it now). For a nice long read, you can find a nice roundup of all TSQLTuesdays over here.
This month, the blog party is all about hacking your database – err hacking your database life. Jess Pomfret (b | t) wants us to each share some of the best tips we have that can help save us oodles of time or can simplify our job significantly. These has been a growing phenomenon of late commonly called life hacks. You can find the invite from Jes – here.
I have a plethora of hacks of all sorts. But since we are supposed to focus on “life” hacks, my plethora is reduced to a smaller plethora. Whittling down that smaller plethora, I have a few that I really must recommend. A couple of these have appeared at other times in some of my articles. Check out my top 3!
Solutions
This is one that I have been using for most of my career. I started using Solutions in SSMS to help organize my scripts and keep them handy for quick use. The amount of time saved there is beyond count at this point. I adopted this method later in my career to also use it when giving presentations.
By adding all of my scripts for a presentation to a solution, it is a quick and efficient way to organize my scripts for use through the presentation. In addition, I have the solution saved so I can quickly reload it at any time when preparing to give the presentation again. Open the solution – BAM! I am ready to go.
As you can see in the associated image, I don’t just do a single presentation per solution. I create one solution to house all of my regular presentations. An added benefit to this is that I will have it already loaded just in case there is a pertinent question that can be best explained through the use of a demo from a different presentation.
I explain in more detail how to use solutions from my previous post on the topic – here.
Mapping it Out
This feature is one of my favorite features still – almost four years later. Even better is that MANY data professionals are still surprised to learn that it exists. I wrote about the scrollbar map in this article and here is a quick synopsis of the feature.
In the following image, I have highlighted the scrollbar as it is configured in SSMS for all instances where I use SSMS. Notice how it looks somewhat different than a typical scrollbar! To help point it out, I have circled the scrollbar in red.
How do we enable that map? That is easy, right click the scrollbar itself (as shown in the following image) and then select “Scroll Bar Options…”
After clicking that menu option, the following window will open.
Circled in red is the set of configurations that will control the look and behavior of the scrollbar. Personally, I prefer map mode over bar mode. Map mode also comes with the option of a “source preview” that will show the code as I hover over the map (as shown in the first image). Again, using this tool, I can navigate the code so much faster and save soooo much time, it is a must have.
Clickable TSQL
One of my favorite time saving hacks is the ability to quickly find code that references a specific object. In addition to being able to quickly find the desired object references in code, I like to be able to display the referencing code quickly without having to go search for it in source control or in SSMS by name. This is where a handy dandy script that formats the code into clickable format is helpful. Not only does it give me the ability to display the code – but it displays it in the format that it was committed to the database.
SELECT OBJECT_NAME(asm.object_id) AS ObjName, (SELECT REPLACE
(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
CONVERT
(
NVARCHAR(MAX),
N'--' + NCHAR(13) + NCHAR(10) + asm.definition + NCHAR(13) + NCHAR(10) + N'--' COLLATE Latin1_General_Bin2
)
,NCHAR(31),N'?'),NCHAR(30),N'?'),NCHAR(29),N'?'),NCHAR(28),N'?')
,NCHAR(27),N'?'),NCHAR(26),N'?'),NCHAR(25),N'?'),NCHAR(24),N'?')
,NCHAR(23),N'?'),NCHAR(22),N'?'),NCHAR(21),N'?'),NCHAR(20),N'?')
,NCHAR(19),N'?'),NCHAR(18),N'?'),NCHAR(17),N'?'),NCHAR(16),N'?')
,NCHAR(15),N'?'),NCHAR(14),N'?'),NCHAR(12),N'?'),NCHAR(11),N'?')
,NCHAR(8),N'?'),NCHAR(7),N'?'),NCHAR(6),N'?'),NCHAR(5),N'?')
,NCHAR(4),N'?'),NCHAR(3),N'?'),NCHAR(2),N'?'),NCHAR(1),N'?'),NCHAR(0),N''
) AS [processing-instruction(query)]
FOR XML PATH(''), TYPE) AS QUERYDEF
FROM sys.all_sql_modules asm
WHERE asm.definition LIKE '%#tempResultTable%'All of the credit for this code nugget goes to Adam Machanic (b | t). He took the time to develop this method and to map out the “?”-able characters appropriately.
When running this code, I get something like the following.
When clicking the link illustrated in the preceding image (big red arrow pointing to it), I get to see something like the following.
Note here that whatever was committed to the database, that is what is being displayed. Sure, I put a sarcastic comment in the green box about the developers notes (entirely because it is all XML and is poorly written for the SQL side imo), but there is also the important illustration in red showing the Create Procedure statement where the stored proc is actually being created. Furthermore, this is the exact format in which the proc was committed to the database. Now, I have have just saved myself some time every time (potentially dozens of times a day) that I have to inquire of the database for a specific referenced object and all of the code that references that object.
Wrapping it Up
There you have it – three very powerful but simple hacks that can give you back dozens of minutes per day. These hacks can help you better navigate code and respond to some of those interrupt requests requiring either some referencing code or maybe to troubleshoot an issue that just cropped up. Check them out and try them. I am sure you will find they will save you oodles of time too!
Feel free to explore some of the other TSQL Tuesday posts I have written.
If you are in need of a little tune-up for your XE skills, I recommend reading a bit on Extended Events to get up to date. For some “back to basics” related articles, feel free to read here.