Introduction

This month for the T-SQL Tuesday post, the subject is short bits of code. Code you use all the time. Some of your daily toolbox scripts if you will. All of my scripts whether daily or less frequently are TSQL or PowerShell. Most of the time it’s both. PowerShell sending a query to SQL Server and doing something with the results. Here are some bits of code I use regularly.

PowerShell

Find files older than 30 days

Frequently I find it helpful to narrow down files in folders to files that are older than x. Using the -Recurse switch is handy for broader search. 

    
        
Get-ChildItem | Where-Object {$_.LastWriteTime -lt (Get-Date).AddDays(-30)}

Version of Windows Server

Having an estate in the progress upgrades sometimes has me wondering what version of Windows Server I have remoted into. 

    
        
(Get-CimInstance win32_operatingsystem).Caption

Last Reboot

Quickly finding the uptime of a server is frequently useful. 

    
        
# Query Last reboot usually from a remote PowerShell session.
$os = (Get-CimInstance win32_operatingsystem)
$os.LastBootUpTime
# Take the same information and compute days since.
(Get-Date) - ($os.LastBootUpTime) | Select-Object Days, Hours

T-SQL

SQL Login expiration

For the rare times I have to use a SQL Login, I need to know when my login expires. I have one server that I use PowerShell and dbatools to apply scripts to. I run this query at beginning to display before applying the scripts. 

    
        
-- Display login expiration for SQL Auth logins.
select LOGINPROPERTY(system_user,'DaysUntilExpiration') as DaysUntil,DATEADD(dd, CONVERT(int, LOGINPROPERTY (system_user, 'DaysUntilExpiration')) , getdate()) AS PasswordExpiration

Conclusion

This is a shorter blog than usual. I’m sure there are many more tools I use on a regular basis that I’m forgetting. I’ll add new collections of tools as I collect them.

Thanks to John McCormack for hosting this months T-SQL Tuesday and thanks for reading.

Check out my other T-SQL Tuesday posts.