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? The return on investment can be shown in longer scripts with more reused variables. 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 gives us 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, many or zero parameters.

As you will have seen in the examples so far, the splatting hash table can take as many parameters as you need, including zero. I show how zero parameters can be used in the below example. I use the IF statement to determine if $localtarget is true or false (1/0). If false, it will add the cimsession argument to the $remotetarget splat table. If false, it will leave that $remotetarget splat table blank. Which is barely different than adding a single space to your command.

Parameters that take multiple values can be used as well.

Per the question posed by the reader below, you can also have parameters that take a list as an argument. Here is an example for using splatting with select-object, Where-object and Sort-object.

$inst = “Labsql1” # Replace with your SQL Server instance name
$srv = Get-Dbadatabase -sqlinstance $inst

$selectsplat = @{
    Property = 'Computername','name','status','owner','Encrypted'
}

$exclusions = 'tempdb','model'

$wheresplat = @{
    FilterScript = { $_.status -eq "Normal" `
                -and $_.name -notin $exclusions
               }
}

$sortsplat = @{
    Property = @(
        @{Expression = "Owner"; Descending = $true}
     ,  @{Expression = "name"; Ascending = $true}
    )
}

$srv | Out-GridView
$srv | Select-Object @SelectSplat | Out-GridView
$srv | Select-Object @SelectSplat | Where-object @WhereSplat | Out-GridView
$srv | Select-Object @SelectSplat | Where-object @WhereSplat | sort-object @SortSplat | Out-GridView

Here are what the outputs look like from these options:

Splatting does not prevent you from using other arguments.

As in the example further 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.