Executes *.sql files and times the execution

SqlExecTime.ps1

# Sample Usage: .\SqlExecTime.ps1 -Server "LOCALHOST" -Database "WideWorldImportersDW" -Integrated
# Prerequisites: Install-Module -Name SqlServer -AllowClobber
# To do: add support for Access Token authentication

param (
	[Parameter(Mandatory=$false)]$Server,
	[Parameter(Mandatory=$false)]$Database,
	[Parameter(Mandatory=$false)]$Username,
	[Parameter(Mandatory=$false)]$Password,
	[Parameter(Mandatory=$false)]$Path,
	[Parameter(Mandatory=$false)]$Include,
	[switch]$Integrated = $false,
	[switch]$ShowOutput = $false
)

try{
	if ($Server -eq $null) {
		$Server = Read-Host 'Server'
	}

	if ($Database -eq $null) {
		$Database = Read-Host 'Database'
	}
	
	if ($Integrated -ne $true) {
		if ($Username -eq $null) {
			$Username = Read-Host 'Username (leave blank for intergrated authentication)'
		}

		if ([string]::IsNullOrEmpty($Username) -ne $true -and $Password -eq $null) {
			$SecurePassword = Read-Host 'Password' -assecurestring
			if ([string]::IsNullOrEmpty($SecurePassword) -ne $true) {
				$Password = [System.Runtime.InteropServices.Marshal]::PtrToStringAuto([System.Runtime.InteropServices.Marshal]::SecureStringToBSTR($SecurePassword))
			}
		}
	}
	
	if ($Path -eq $null) {
		$Path = $(Get-Location)
	}
	
	if ($Include -eq $null) {
		$Include = "*.sql"
	}
	
	if ($Verbose) {
		$VerbosePreference = "Continue"
	}

	Write-Verbose "----------------------------------------"
	Write-Verbose "Looking for *.sql files on $Path"
	Write-Verbose "----------------------------------------"
	$files = Get-ChildItem -Path "$Path\*" -Include $Include
	Write-Verbose "$($files.Count) file(s) found"
	Write-Verbose "----------------------------------------"
	
	$results = New-Object System.Collections.ArrayList
	
	foreach ($f in $files){
		Write-Verbose "Executing $($f.Name)"
		[int]$start = (Get-Date).Millisecond
		$output = $(If ([string]::IsNullOrEmpty($Username) -or [string]::IsNullOrEmpty($Password)) {
			invoke-sqlcmd -inputfile $f.FullName -ServerInstance $Server -Database $Database -OutputAs DataRows -StatisticsVariable stats
		} Else {
			invoke-sqlcmd -inputfile $f.FullName -ServerInstance $Server -Database $Database -Username $Username -Password $Password -OutputAs DataRows -StatisticsVariable stats
		})
		
		[int]$end = (Get-Date).Millisecond
		
		if ($ShowOutput) {
			Write-Output $output
		}
		
		Write-Verbose "number of rows affected     : $($stats.IduRows)"
		Write-Verbose "number of insert statements : $($stats.IduCount)"
		Write-Verbose "number of select statements : $($stats.SelectCount)"
		Write-Verbose "server execution time       : $($stats.ExecutionTime)ms"
		Write-Verbose "end to end execution time   : $($stats.ExecutionTime)ms"
		Write-Verbose "----------------------------------------"
		 
		$temp = New-Object System.Object
		$temp | Add-Member -MemberType NoteProperty -Name "FileName" -Value $f.Name
		$temp | Add-Member -MemberType NoteProperty -Name "Server Execution Time(ms)" -Value $stats.ExecutionTime
		$temp | Add-Member -MemberType NoteProperty -Name "Client Execution Time(ms)" -Value $stats.ConnectionTime
		$results.Add($temp) | Out-Null
	}
	
	$results | Format-Table -AutoSize
}
catch {
	Write-Verbose "Script execution aborted."
	Write-Error -Message "$($_.Exception.Message)"
}