Time to read: ~ 2 minutes
Words: 328
Welcome to T-SQL Tuesday, the monthly blog post invitational where we’re given a topic and asked to write about it.
This month we have John McCormack (Blog | Twitter) asking, “What are your go-to handy scripts“?
For this post, I’m going to break these down into different languages.
SQL
I once had the annoyingly complex T-SQL to change MS format time into a human-readable format memorised.
SELECT
time_MS_format = [TimeMSFormat],
converted_time = '2021-10-12 ' +
STUFF(
STUFF(
RIGHT('000000' + X.TimeMSFormat, 6), 3, 0, ':'
), 6, 0, ':'
)
FROM (VALUES
('00000')
, ('00500')
, ('01000')
, ('10000')
, ('10500')
, ('100000')
, ('100500')
, ('115500')
, ('120000')
) X ([TimeMSFormat]);
Then I read a blog post from Kenneth Fisher (Blog | Twitter) about the in-built msdb database function dbo.agent_datetime.
SELECT
time_MS_format = [TimeMSFormat],
new_function = msdb.dbo.agent_datetime(20211012, X.TimeMSFormat)
FROM (VALUES
('00000')
, ('00500')
, ('01000')
, ('10000')
, ('10500')
, ('100000')
, ('100500')
, ('115500')
, ('120000')
) X ([TimeMSFormat]);
If I run sp_helptext on that function, it reminds me of that Andy Mallon (Blog | Twitter) post.
It would be more performant if I stripped that function and used the code directly but the code is too handy to use for the infrequent times I need it.
PowerShell
I’ve talked before about using ConvertTo-SqlSelect in a blog post before and I still use that function alot!
Another short piece of code that I use is more for formatting than anything else. You can populate a variable with an array of properties names. Select-Object can use this variable to return information.
$Properties = 'SqlInstance', @{Name = 'DatabaseName'; Expression = {$_.Name}}, 'Status', 'RecoveryModel', 'Owner'
Get-DbaDatabase -SqlInstance localhost -SqlCredential $Cred | Select $Properties
A useful snipper for reporting is to use a combination of Sort-Object and the Format-* commands with the -GroupBy parameter.
Get-DbaDatabase -SqlInstance localhost -SqlCredential $Cred |
Select $Properties |
Sort-Object RecoveryModel |
Format-Table -GroupBy RecoveryModel
Sin é
When I sit down and write this post, I realise that I don’t have a lot of handy scripts. Either I re-write things constantly (that’s likely), or I don’t know enough yet (also likely). I should fix that.
