PowerShell Splatting 101: Clean Up Your Commands

Have you ever written a PowerShell command so long that it stretched across the screen? Or had to update a script and hunt through a long parameter list to change a single value? Splatting solves that problem by letting you pass multiple parameters to a command using a single variable.

What Is Splatting?

Splatting allows you to store parameters in a hashtable and pass them to a command with a simple @Variable reference. This keeps scripts cleaner, easier to read, and easier to maintain.

Compare a traditional command:

Get-ChildItem -Path "C:\Temp" -Filter "*.log" -Recurse

…to a splatted version:

$Params = @{
    Path    = "C:\Temp"
    Filter  = "*.log"
    Recurse = $true
}
Get-ChildItem @Params

Much cleaner, right? Here is a before and after snippet of my own code before adding splatting to clean things up:

#Before
        Invoke-DbaQuery -SqlInstance $SQLInstance - SQLCredential $Cred -Database 'msdb' -query 'sp_update_job' -commandtype 'StoredProcedure' -SqlParameter @{ job_name = "_MAINT_DatabaseBackup - USER_DATABASES - FULL";      enabled = $UserFULLBackups} 
        Invoke-DbaQuery -SqlInstance $SQLInstance - SQLCredential $Cred -Database 'msdb' -query 'sp_update_job' -commandtype 'StoredProcedure' -SqlParameter @{ job_name = "_MAINT_DatabaseBackup - USER_DATABASES - DIFF";      enabled = $UserDIFFBackups} 
        Invoke-DbaQuery -SqlInstance $SQLInstance - SQLCredential $Cred -Database 'msdb' -query 'sp_update_job' -commandtype 'StoredProcedure' -SqlParameter @{ job_name = "_MAINT_DatabaseBackup - USER_DATABASES - LOG";       enabled = $UserLOGBackups} 
        Invoke-DbaQuery -SqlInstance $SQLInstance - SQLCredential $Cred -Database 'msdb' -query 'sp_update_job' -commandtype 'StoredProcedure' -SqlParameter @{ job_name = "_MAINT_DatabaseBackup - SYSTEM_DATABASES - FULL";    enabled = $SystemFULLBackups} 
        Invoke-DbaQuery -SqlInstance $SQLInstance - SQLCredential $Cred -Database 'msdb' -query 'sp_update_job' -commandtype 'StoredProcedure' -SqlParameter @{ job_name = "_MAINT_DatabaseIntegritycheck - SYSTEM_DATABASES";   enabled = $SystemIntegrity} 
        Invoke-DbaQuery -SqlInstance $SQLInstance - SQLCredential $Cred -Database 'msdb' -query 'sp_update_job' -commandtype 'StoredProcedure' -SqlParameter @{ job_name = "_MAINT_DatabaseIntegritycheck - USER_DATABASES";     enabled = $UserIntegrity} 
        Invoke-DbaQuery -SqlInstance $SQLInstance - SQLCredential $Cred -Database 'msdb' -query 'sp_update_job' -commandtype 'StoredProcedure' -SqlParameter @{ job_name = "_MAINT_IndexOptimize - USER_DATABASES";              enabled = $indexOptimize} 
        Invoke-DbaQuery -SqlInstance $SQLInstance - SQLCredential $Cred -Database 'msdb' -query 'sp_update_job' -commandtype 'StoredProcedure' -SqlParameter @{ job_name = "_MAINT_CommandLog Cleanup";                          enabled = $CommandLogCleanup} 
        Invoke-DbaQuery -SqlInstance $SQLInstance - SQLCredential $Cred -Database 'msdb' -query 'sp_update_job' -commandtype 'StoredProcedure' -SqlParameter @{ job_name = "_MAINT_CycleErrorLog";                               enabled = $CycleErrorLog} 
        Invoke-DbaQuery -SqlInstance $SQLInstance - SQLCredential $Cred -Database 'msdb' -query 'sp_update_job' -commandtype 'StoredProcedure' -SqlParameter @{ job_name = "_MAINT_Output File Cleanup";                         enabled = $OutputFileCleanup} 
        Invoke-DbaQuery -SqlInstance $SQLInstance - SQLCredential $Cred -Database 'msdb' -query 'sp_update_job' -commandtype 'StoredProcedure' -SqlParameter @{ job_name = "_MAINT_sp_delete_backuphistory";                     enabled = $DeleteBackupHistory} 
        Invoke-DbaQuery -SqlInstance $SQLInstance - SQLCredential $Cred -Database 'msdb' -query 'sp_update_job' -commandtype 'StoredProcedure' -SqlParameter @{ job_name = "_MAINT_sp_purge_jobhistory";                         enabled = $PurgeJobHistory} 
        Invoke-DbaQuery -SqlInstance $SQLInstance - SQLCredential $Cred -Database 'msdb' -query 'sp_update_job' -commandtype 'StoredProcedure' -SqlParameter @{ job_name = "_MAINT_sp_whoisactive data collection monitoring";   enabled = $EnableWhoisactiveJob} 

And after:

#After
        IF($UseIntegratedAuth -eq 1) { 
            $SqlSplat = @{
                SqlInstance       = $SQLInstance
            }
        } ELSE {
            $SqlSplat = @{
                SqlInstance       = $SQLInstance
                SQLCredential     = $Cred
            }
        }

        $JobUpdatesplat = @{
            Database    = 'msdb'
            query       = 'sp_update_job'
            commandtype = 'StoredProcedure'
        }

        Invoke-DbaQuery @SqlSplat @JobUpdatesplat -SqlParameter @{ job_name = "_MAINT_DatabaseBackup - USER_DATABASES - FULL";      enabled = $UserFULLBackups} 
        Invoke-DbaQuery @SqlSplat @JobUpdatesplat -SqlParameter @{ job_name = "_MAINT_DatabaseBackup - USER_DATABASES - DIFF";      enabled = $UserDIFFBackups} 
        Invoke-DbaQuery @SqlSplat @JobUpdatesplat -SqlParameter @{ job_name = "_MAINT_DatabaseBackup - USER_DATABASES - LOG";       enabled = $UserLOGBackups} 
        Invoke-DbaQuery @SqlSplat @JobUpdatesplat -SqlParameter @{ job_name = "_MAINT_DatabaseBackup - SYSTEM_DATABASES - FULL";    enabled = $SystemFULLBackups} 
        Invoke-DbaQuery @SqlSplat @JobUpdatesplat -SqlParameter @{ job_name = "_MAINT_DatabaseIntegritycheck - SYSTEM_DATABASES";   enabled = $SystemIntegrity} 
        Invoke-DbaQuery @SqlSplat @JobUpdatesplat -SqlParameter @{ job_name = "_MAINT_DatabaseIntegritycheck - USER_DATABASES";     enabled = $UserIntegrity} 
        Invoke-DbaQuery @SqlSplat @JobUpdatesplat -SqlParameter @{ job_name = "_MAINT_IndexOptimize - USER_DATABASES";              enabled = $indexOptimize} 
        Invoke-DbaQuery @SqlSplat @JobUpdatesplat -SqlParameter @{ job_name = "_MAINT_CommandLog Cleanup";                          enabled = $CommandLogCleanup} 
        Invoke-DbaQuery @SqlSplat @JobUpdatesplat -SqlParameter @{ job_name = "_MAINT_CycleErrorLog";                               enabled = $CycleErrorLog} 
        Invoke-DbaQuery @SqlSplat @JobUpdatesplat -SqlParameter @{ job_name = "_MAINT_Output File Cleanup";                         enabled = $OutputFileCleanup} 
        Invoke-DbaQuery @SqlSplat @JobUpdatesplat -SqlParameter @{ job_name = "_MAINT_sp_delete_backuphistory";                     enabled = $DeleteBackupHistory} 
        Invoke-DbaQuery @SqlSplat @JobUpdatesplat -SqlParameter @{ job_name = "_MAINT_sp_purge_jobhistory";                         enabled = $PurgeJobHistory} 
        Invoke-DbaQuery @SqlSplat @JobUpdatesplat -SqlParameter @{ job_name = "_MAINT_sp_whoisactive data collection monitoring";   enabled = $EnableWhoisactiveJob} 

The second is far more readable and easier to update the parameters across all of the commands should any need to change. This also is a good transition to a few more interesting points about splatting.

You can use multiple splats in the same command.

Above I have the @SqlSplat to manage the ‘SqlInstance’ and ‘SQLcredential’ parameters, and the @JobUpdateSplat to handle the specific parameters for these job update commands. This frees me up to use the @SqlSplat for other connections with other DBAtools commands seamlessly.

You can use splats for one or no parameters.

I use the IF statement to determine if $UseIntegratedAuth is true or false. If false, it will prompt for a credential earlier in the script and use that across all dbatools connections, if true it will not include the credential argument. All of this happens before the job update command and allows for more logic in your scripts.

Splatting does not prevent you from using other arguments.

As in the example above, I use two splat tables and the -SQLParameter argument as well.

Here is a quick example of using multiple splats, including an empty splat for registering task scheduler jobs:

#Shared Parameters
                    $commonTasksplat = @{
                        user           = $qualifiedUsername
                        password       = $password
                        runlevel       = 'Highest'
                    }

                # Remote Target Argument
                    IF ($localtarget -eq 1) {$RemoteTarget  = @{}}
                    ELSE                    {$RemoteTarget  = @{Cimsession     = $computername }}

	# Job splat (only sharing one as an example)
                $ICUsplat = @{
                    TaskName       = "_MAINT_Integrity Checks User Database"
                    action         = New-ScheduledTaskAction -Execute "$localifiedTaskScriptDirectory\IntegrityCheckUserDatabases.bat"
                    settings       = New-ScheduledTaskSettingsSet -MultipleInstances IgnoreNew -ExecutionTimeLimit (New-TimeSpan -Days 1) -Priority 7
                    description    = "Checks Integrity of User Databases"
                    trigger        = $saturdaysTrigger
                }

                    Register-ScheduledTask @commonTasksplat @RemoteTarget @SBFsplat | Out-Null
                    Register-ScheduledTask @commonTasksplat @RemoteTarget @UBFsplat | Out-Null
                    Register-ScheduledTask @commonTasksplat @RemoteTarget @UBDsplat | Out-Null
                    Register-ScheduledTask @commonTasksplat @RemoteTarget @UBLsplat | Out-Null
                    Register-ScheduledTask @commonTasksplat @RemoteTarget @CLCsplat | Out-Null
                    Register-ScheduledTask @commonTasksplat @RemoteTarget @DBHsplat | Out-Null
                    Register-ScheduledTask @commonTasksplat @RemoteTarget @IMUsplat | Out-Null
                    Register-ScheduledTask @commonTasksplat @RemoteTarget @ICSsplat | Out-Null
                    Register-ScheduledTask @commonTasksplat @RemoteTarget @ICUsplat | Out-Null

The @RemoteTarget splat is empty unless the target of the script is a remote computer. If it is remote, it will add the cimsession argument with the target computer as the value. This helps with automating SQL Express maintenance rollouts across local and remote targets!

How to Use Splatting

Splatting is pretty straightforward. These are the steps to follow:

Step 1: Create the Variable

Create a variable with whatever name you prefer. Descriptive is usually better.

$SplatExample

Step 2: Assign a Value

Assign the value of the variable as a hash table:

$SplatExample = @{

}

Step 3: Reference the Arguments

Reference the arguments that the command(s) you will be using allow. Create key value pairs inside the hash table as shown below. I am using Invoke-DbaQuery as my example.

$SplatExample = @{
	SQLinstance 		=  ‘Labsql1’ 
	Database		=  ‘master’
	SQLCredential 	= get-credential
}

Step 4: Use the Splat Table

Use the splat table in your command by using an @ before the variable name, PowerShell variables are not case sensitive. Remember, you can use other arguments as well.

$SplatExample = @{
	SQLinstance 		=  ‘Labsql1’ 
	Database		=  ‘master’
	SQLCredential 	= get-credential
}
Invoke-DbaQuery @splatexample -query ‘SELECT @@Servername’

And that’s it! You’ve splatted. Think of this as a way to simplify your commands so that the parts that are actually different are the only thing you see while writing and troubleshooting. It helps to make for cleaner and less cluttered code.

Final Thoughts on Splatting

Splatting is a simple but powerful feature for writing cleaner, more maintainable PowerShell scripts. Next time you see a command with more than three parameters, try refactoring it with splatting. Splatting will make your scripts more readable, easier to maintain, and more reusable. Try it out on your own scripts, you’ll thank yourself later.