PowerShell - Execute a SQL script file

# Import SQL Server module
Import-Module SQLPS -DisableNameChecking

# Not convinced this is needed
# Connect to the server
$instanceName = "localhost"
$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $instanceName



# Not convinced this is needed
# Connect to the database
$dbName = "AdventureWorks2012"
$db = $server.Databases[$dbName]

# Run the query
Invoke-Sqlcmd -InputFile "c:\temp\script.sql" -ServerInstance "$instanceName" -Database $dbName | Select-Object FirstName, LastName, ModifiedDate | Format-Table

# Dashes
Write-Host $('-' * 70)

# Capture the output in an array and display the second record
$output = Invoke-Sqlcmd -InputFile "c:\temp\script.sql" -ServerInstance "$instanceName" -Database $dbName 
$output[1]

# Capture the output to a csv file
Invoke-Sqlcmd -InputFile "c:\temp\script.sql" -ServerInstance "$instanceName" -Database $dbName | Select-Object FirstName, LastName, ModifiedDate | Export-Csv -LiteralPath "c:\temp\output.csv" -NoTypeInformation

PowerShell - Functions

# Displays Hello World
function displayHello
{
    Write-Host "Hello World"
}
displayHello

# Pass a parameter
function displaySomething
{
    Write-Host $args
}
displaySomething 'Hello Again'

# Working with multiple parameters
function addNumbers
{
    Write-Host ($args[0] + $args[1])
}
# Displays 7
addNumbers 3 4

# Fancier way to work with multiple parameters
function AddNumbersAgain
{
    param( [int]$num1, [int]$num2 )
    write-host ($num1 + $num2)
}
# Displays 9
AddNumbersAgain 4 5

PowerShell - Format Commands

# List all processes
Get-Process

# Display 70 dashes so it makes a line
# (This makes it easier to see different output)
Write-Host $('-' * 70)

# Show only SQL processes
Get-Process -Name *sql*

# Dashes
Write-Host $('-' * 70)

# Looks pretty much the same but with column headers
Get-Process -Name *sql* | Format-Table

# Unfortunately this truncates the output
Get-Process -Name *sql* | Format-Table -Property Path, Name, Id, Company

# Better because we can now see the Path but depending on console size Company is still truncated
Get-Process -Name *sql* | Format-Table -Property Path, Name, Id, Company -AutoSize

# Wraps output so we can see everything
Get-Process -Name *sql* | Format-Table -Wrap -Property Path, Name, Id, Company

# Groups all processes by Company 
Get-Process | Format-Table -Wrap -AutoSize -Property Path, Name, Id -GroupBy Company | more

# Displays all processes in wide format.
# Format-Wide only displays the default property by default.
# The net result is that this shows us each process in a multi column list.
Get-Process | Format-Wide

# Shows us the same thing. I guess Name is the default property.
Get-Process | Format-Wide -Property Name

# Still a multi-column list but this time company names.
Get-Process | Format-Wide -Property Company

# Shows each process with one line per property
Get-Process | Format-List

# Creates an array of processes
$processes = Get-Process
# Displays the fifth process in the array
$processes[4]

# Dashes
Write-Host $('-' * 70)

# Alias for Format-List
Get-Process | fl

# Alias for Format-Wide
Get-Process | fw

# Alias for Format-Table
Get-Process | ft

PowerShell - Error Control

$numerator = 3
$denominator = 0

try
{
    $myVar = $numerator / $denominator
}

catch
{
    Write-Host "Something went wrong:"
    Write-Host $error[0]
}

finally
{

    Write-Host "Do this regardless"
}

PowerShell - Loops

# While loop
# Counts from 1 to 3
$myVar = 1
while ($myVar -le 3)
{
    Write-Host $myVar
    $myVar++
}

---

# Do While loop
Write-Host "Another way to count from 1 to 3"
$myVar = 1
do
{
    $myVar
    $myVar++
} while ($myVar -le 3)

---

# For loop
Write-Host "And another way"
for ($myVar = 1; $myVar -le 3; $myVar++ )
    {
    $myVar
    }

---

# Now do it with a hash
$myArray = @(1, 2, 3)
Write-Host "Geting fancier"
foreach($myVar in $myArray)
{
    Write-Host "Value $myVar"
}

PowerShell - Syntax

Display output in the console:

Write-Host "Hello World"

Variable substitution:

$myvar = " World"
# Displays 'Hello World'
Write-Host "Hello$myvar"

<#
This is how I would
make a block comment
of several lines
#>

Write-Host "Goodbye$myvar"

---

If/Then/Else:

# This has some problems when the user enters an age less than 10.

# For the sake of learning, enter an age of 10 or higher when prompted.

# Your browser may not show it, but there is a space between $age and -lt

$age = Read-Host "How old are you?"

if ($age -lt 13)

{

    Write-Host "Child"

}

elseif ($age -lt 18)

{

    Write-Host "Teen"

}

else 

{

    Write-Host "Adult"

}

---

Switch (case):

# This has some problems when the user enters an age less than 10.

# For the sake of learning, enter an age of 10 or higher when prompted.

$age = Read-Host "How old are you?"

switch ($age)

{

    {$_ -lt 13 }

    {

        Write-Host "Child"

    }

    {($_ -lt 18) -and ($_ -gt 12) }

    {

        Write-Host "Teen"

    }

    default

    {

        Write-Host "Adult"

    }

}

---

Arrays:

# Empty array:
$myArray = @()

# Populated array:
$myArray = "baby", "toddler", "child", "teen", "adult"
$collectionOfAges = 0, 4, 13, 18

#Another way to define arrays:
$myArray = @("baby", "toddler", "child", "teen", "adult")

# Interact with arrays:
# child:
$myArray[2]

#
# This will not work because $myArray is a fixed array
# $myArray.Add("senior")
# 
# So instead we need a dynamic-sized array.
# Here is how to define one:

$myArray = New-Object System.Collections.ArrayList
$myArray.Add("baby")
$myArray.Add("toddler")
$myArray.Add("child")
$myArray.Add("teen")
$myArray.Add("adult")
$myArray.Add("senior")

# senior:
$myArray[5]

Write-Host "non-adults:"
$myArray[0..2]

---

Hashes:

$myHash = @{
"ford" = "mustang"
"pontiac"  = "solstice"
"chevrolet" = "camaro"
}

# Displays 'mustang'
$myHash["ford"]

# Also displays 'mustang'
$myHash.ford


# Displays 3
$myHash.Count

PowerShell - SQL Module

How to load SQL PowerShell module:

Import-Module SQLPS