A couple of CMDlets to interrogate a SQLServer

SQLServer.ps1

<#
    .Synopsis
    Opens a connection to a SQL server.
    .DESCRIPTION
    Opens a connection to a SQL server and provides a sqlConnection object.
    the connection object has to be closed at the end of the processing.
    .EXAMPLE
    $con = Open-SQLServer -Server SQLSVR -Database MyDataBase
    Opens a connection to the MyDataBase DB on SQLSVR.
    .LINK
    Close-SQLServer
    Invoke-SQL
    Get-SQLData
#>
function Open-SQLServer
{
    [CmdletBinding()]
    [OutputType([System.Data.SqlClient.SqlConnection])]
    Param
    (
        # Server name
        [Parameter(Mandatory = $false,
                   ValueFromPipelineByPropertyName = $true,
                   Position = 0)]
        [Alias('SQLServer')]
        [string]
        $Server,

        # Connection string. Will take other parameters into account.
        [string]
        $ConnectionString = "Integrated Security=SSPI;Persist Security Info=True;Data Source=${Server};Packet Size=4096;",

        # Database to access
        [string]
        $Database
    )

    Begin
    {
        $ConnectionParams = @{}
        foreach ($parameter in ($ConnectionString -split ';'))
        {
            $parampair = $parameter -split '='
            If ($parampair[1])
            {
                $ConnectionParams += @{
                    $parampair[0] = $parampair[1]
                }
            }
        }
        If ($Server)
        {
            foreach ($parameter in 'Data Source', 'Server', 'Address', 'Addr''Network Address')
            {
                $ConnectionParams.Remove($parameter)
            }
            $ConnectionParams += @{
                'Server' = $Server
            }
        }
        If ($Database)
        {
            foreach ($parameter in 'Initial Catalog', 'Database')
            {
                $ConnectionParams.Remove($parameter)
            }
            $ConnectionParams += @{
                'Database' = $Database
            }
        }
        $newConnectString = @()
        foreach ($parameter in $ConnectionParams.Keys)
        {
            $newConnectString += $parameter+'='+$ConnectionParams[$parameter]
        }
        $ConnectionString = $newConnectString -join ';'
    }
    Process
    {
        Write-Debug -Message $ConnectionString
        $connection = New-Object -TypeName System.Data.SqlClient.SqlConnection
        $connection.ConnectionString = $ConnectionString
        $connection.Open()
        Return $connection
    }
}

<#
    .Synopsis
    Closes a SQL server connection
    .DESCRIPTION
    Closes an existing SQL server connection. Mandatory for correct cleaning.
    .EXAMPLE
    Close-SQLServer -con $con
    .LINK
    Open-SQLServer
    Invoke-SQL
    Get-SQLData
#>
function Close-SQLServer
{
    [CmdletBinding()]
    #[OutputType([int])]
    Param
    (
        # SqlConnection. Must be open.
        [Parameter(Mandatory = $true,
                   ValueFromPipeline = $true)]
        [System.Data.SqlClient.SqlConnection]$con
    )
    Process
    {
        $con.Close()
    }
}

<#
    .Synopsis
    Gets data from a SQL server
    .DESCRIPTION
    Returns a set of rows from a SQL server either with a direct SQL query
    or by selecting a table. Additional filtering is possible. A pre-existing
    open connection must be provided.
    .EXAMPLE
    Get-SQLData -con $con -SQL "SELECT name,address,age FROM Users WHERE name = 'John Doe'"
    Returns the name, address and age of every user named 'John Doe'
    .EXAMPLE
    Get-SQLData -con $con -Table Users -Properties name,address,age -Where "name = 'John Doe'"
    Returns the name, address and age of every user named 'John Doe'
    .LINK
    Open-SQLServer
    Close-SQLServer
    Invoke-SQL
#>
Function Get-SQLData
{
    [CmdletBinding()]
    [OutputType([System.Data.DataRow])]
    Param   
    (
        # SqlConnection. Must be open.
        [Parameter(Mandatory = $true)]
        [System.Data.SqlClient.SqlConnection]$con,
        
        # SQL query
        [Parameter(Mandatory = $true,
        ParameterSetName = 'SQL')]
        [String]$SQL,
        
        
        # Table to get
        [Parameter(Mandatory = $true,
        ParameterSetName = 'Table')]
        [String]$Table,
    
        # Where clause.
        [Parameter(ParameterSetName = 'Table')]
        [String]$Where,
        
        # Properties subset.
        [Parameter(ParameterSetName = 'Table')]
        [String[]]$Properties = '*',
        
        # Internal Use.
        [string]
        $AddType = $Null
            )
    Begin
    {
        If ($Table)
        {
            $SQL = "SELECT $($Properties -join ',') FROM ${Table} "
            If ($Where)
            {
                $SQL += "WHERE ${Where} "
            }
        }
    }
    Process
    {
        $dataset = Invoke-SQL -con $con -SQL $SQL
        If ($AddType) 
        {
            foreach ($row in $dataset.Tables.Rows) 
            {
                $row.PSTypeNames.Insert(0,$AddType) 
            }
        }
        Return $dataset.Tables[0]
    }
}