T-SQL Tuesday #143 – Handy Short Scripts

The invitation for the October edition of T-SQL Tuesday comes from John McCormack. John wants to know the best “go to handy short scripts” to have in your arsenal, whether it be T-SQL or other languages. Click on the image below to check out the full invitation blog post:

SQL Server Version Check

The most common, short script I could think of may very well be one of the shortest T-SQL scripts that can be executed. When you want to know what version of SQL Server you are connected to, you can run this script:

SELECT @@VERSION;

I’ve used this script to verify the version before/after an upgrade and to check versions if doing work on a server for the first time.

Last SQL Restart

Another short script that came to mind was this script to check the date when SQL Server last restarted:

SELECT login_time
FROM sys.dm_exec_sessions
WHERE session_id = 1;

This can be helpful when troubleshooting a query issue to know what can be expected in plan cache.

Querying for Stored Procedure Text

In the invitation post, one example script is used for searching SQL jobs for a T-SQL string. That script reminded me of a similar script that I use for searching stored procedures for a string:

SELECT name, xtype
FROM sysobjects sysobj
INNER JOIN syscomments syscom ON sysobj.id = syscom.id
WHERE xtype = 'P'
	AND syscom.text LIKE '%SearchText%';

This script can easily be modified to search for text in objects like functions, views, and more by specifying the xtype. Click here to check out sys.sysobjects and potential xtype values.

Deploying FRK

I use a short PowerShell script for deploying First Responder Kit updates to servers. I liked Example 5 on https://docs.dbatools.io/Install-DbaFirstResponderKit and setup variables containing lists of servers. This saves having to update each server one at a time and makes it much less likely to leave a server out:

# Servers to update
$nonprodservers= "SERVER01","SERVER02"
$prodservers = "SERVER03","SERVER04"

# Deploy to server group
$prodservers | Install-DbaFirstResponderKit

If a new server is brought online, I’ll add it to the appropriate server list.

Something Short and Sweet

The scripts above aren’t anything too crazy but show that a script doesn’t have to be complicated or long to be useful.

Thanks for reading!