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:
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.
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!
2 thoughts on “T-SQL Tuesday #143 – Handy Short Scripts”
As an alternative to your script for finding a text string in a procedure you might use:
Select Object_Name (object_ID) as procedureName
Where definition LIKE ‘%sqltexttosearch%’
Have a great day.