Yodel.psm1


#Requires -Version 5.1
Set-StrictMode -Version 'Latest'

# Functions should use $script:moduleRoot as the relative root from which to find
# things. A published module has its function appended to this file, while a
# module in development has its functions in the Functions directory.
$script:moduleRoot = $PSScriptRoot

enum Yodel_MsSql_QueryKeyword
{
    Default = 1
}

# Store each of your module's functions in its own file in the Functions
# directory. On the build server, your module's functions will be appended to
# this file, so only dot-source files that exist on the file system. This allows
# developers to work on a module without having to build it first. Grab all the
# functions that are in their own files.
$functionsPath = & {
    Join-Path -Path $script:moduleRoot -ChildPath 'Functions\*.ps1'
    Join-Path -Path $script:moduleRoot -ChildPath 'Functions\MsSql\*.ps1'
}
foreach( $functionPath in (Get-Item $functionsPath) )
{
    if ( -not (Test-Path -Path $functionPath))
    {
        continue
    }


    . (Get-Item -Path $functionPath).FullName
}



function Connect-YDatabase
{
    <#
    .SYNOPSIS
    Opens an ADO.NET connection to a database.
 
    .DESCRIPTION
    The `Connect-YDatabase` function opens an ADO.NET (i.e. pure .NET) connection to a database. Pass the connection string to the `ConnectionString` parameter. Pass the provider to use to connect to the `Provider` parameter. This parameter should be an instance of a `Data.Common.DbProviderFactory` object. The .NET framework ships with several:
 
    * SQL Server: `Connect-YDatabase -Provider ([Data.SqlClient.SqlClientFactory]::Instance)`
    * ODBC: `Connect-YDatabase -Provider ([Data.Odbc.OdbcFactory]::Instance)`
    * OLE: `Connect-YDatabase -Provider ([Data.OleDb.OleDbFactory]::Instance)`
    * Entity Framework: `Connect-YDatabase -Provider ([Data.EntityClient.EntityProviderFactory]::Instance)`
    * Oracle: `Connect-YDatabase -Provider ([Data.OracleClient.OracleClientFactory]::Instance)`
 
    The function uses each provider to create a connection object, sets that connection's connection string, open the connection, and then returns the connection.
 
    The `Connect-YDatabase` also has a simplified parameter set to open a connection to SQL Server. Pass the SQL Server name (e.g. `HOST\INSTANCE`) to the `SqlServerName` parameter, the database name to the `DatabaseName` parameter, and any other connection properties to the `ConnectionString` property. The function will create a connection to the SQL Server database using integrated authentication. To connect as a specific user, pass that user's credentials to the `Credential` parameter. ADO.NET requires that the credential's password be in read-only mode, so `Connect-YDatabase` will call `$Credential.Password.MakeReadOnly()`.
 
    Returns a `Data.Common.DbConnection` object, the base class for all ADO.NET connections. You are responsible for closing the connection:
 
        $conn = Connect-YDatabase -SqlServerName '.' -DatabaseName 'master'
        try
        {
            # run some queries
        }
        finally
        {
            # YOU MUST DO THIS!
            $conn.Close()
        }
 
    .EXAMPLE
    Connect-YDatabase -SqlServerName '.' -DatabaseName 'master'
 
    Demonstrates how to connect to Microsoft SQL Server using integrated authentiction.
 
    .EXAMPLE
    Connect-YDatabase -SqlServerName '.' -DatabaseName 'master' -Credential $credential
 
    Demonstrates how to connect to Microsoft SQL Server as a specific user. The `$credential` parameter must be `PSCredential` object.
 
    .EXAMPLE
    Connect-YDatabase -SqlServerName '.' -DatabaseName 'master' -ConnectionString 'Application Name=Yodel;Workstation ID=SomeComputerName'
 
    Demonstrates how to supply additional connection string properties when using the SQL Server parameter set.
 
    .EXAMPLE
    Connect-YDatabase -Provider ([Data.Odbc.OdbcFactory]::Instance) -ConnectionString 'some connection string'
 
    Demonstrates how to connect to a database using ODBC.
 
    .EXAMPLE
    Connect-YDatabase -Provider ([Data.OleDb.OleDbFactory]::Instance) -ConnectionString 'some connection string'
 
    Demonstrates how to connect to a database using OLE.
 
    .EXAMPLE
    Connect-YDatabase -Provider ([Data.EntityClient.EntityProviderFactory]::Instance) -ConnectionString 'some connection string'
 
    Demonstrates how to connect to a database using the Entity Framework provider.
 
    .EXAMPLE
    Connect-YDatabase -Provider ([[Data.OracleClient.OracleClientFactory]::Instance) -ConnectionString 'some connection string'
 
    Demonstrates how to connect to a database using Oracle.
    #>

    [CmdletBinding()]
    [OutputType([Data.Common.DbConnection])]
    param(
        [Parameter(Mandatory, ParameterSetName='SqlServer')]
        [String]$SqlServerName,

        [Parameter(Mandatory, ParameterSetName='SqlServer')]
        [String]$DatabaseName,

        [Parameter(ParameterSetName='SqlServer')]
        [pscredential]$Credential,

        [Parameter(Mandatory, ParameterSetName='Generic')]
        [Data.Common.DbProviderFactory] $Provider,

        # The connection string to use.
        [String]$ConnectionString,

        # The connection timeout. By default, uses the .NET default of 30 seconds. If it takes longer than this number of seconds to connect, the function will fail.
        #
        # Setting this property adds a `Connection Timeout` property to the connection string if you're connecting to a SQL Server database (i.e. using the `SqlServerName` parameter). If you're connecting via ODBC, the `ConnectionTimeout` property is set. In all other cases, this parameter is ignored.
        #
        # If you get an error that `ConnectionTimeout` is a read-only property, you'll need to pass the timeout as a property in your connection string.
        [int]$ConnectionTimeout
    )

    Set-StrictMode -Version 'Latest'
    Use-CallerPreference -Cmdlet $PSCmdlet -SessionState $ExecutionContext.SessionState

    if( $PSCmdlet.ParameterSetName -eq 'SqlServer' )
    {
        $Provider = [Data.SqlClient.SqlClientFactory]::Instance
    }

    $connection = $Provider.CreateConnection()
    $connStringBuilder = $Provider.CreateConnectionStringBuilder()

    if( $ConnectionString )
    {
        # There's some weird PowerShell magic going on when setting the ConnectionString property, so directly call the setter function.
        $connStringBuilder.set_ConnectionString($ConnectionString)
    }

    if( $PSCmdlet.ParameterSetName -eq 'SqlServer' )
    {
        $connStringBuilder['Server'] = $SqlServerName
        $connStringBuilder['Database'] = $DatabaseName

        if( $Credential )
        {
            $Credential.Password.MakeReadOnly()
            $sqlCredential = [Data.SqlClient.SqlCredential]::new($Credential.UserName, $Credential.Password)
            $connection.Credential = $sqlCredential
        }
        else
        {
            $connStringBuilder['Integrated Security'] = 'True'
        }

        if( $ConnectionTimeout )
        {
            $connStringBuilder['Connection Timeout'] = $ConnectionTimeout
        }

        $ConnectionString = $connStringBuilder.ToString()
    }
    else
    {
        if( $ConnectionTimeout )
        {
            $connection.ConnectionTimeout = $ConnectionTimeout
        }
    }

    $connection.ConnectionString = $ConnectionString
    $connection.Open()
    return $connection
}


function Invoke-YDbCommand
{
    <#
    .SYNOPSIS
    Uses ADO.NET to execute a database command.
 
    .DESCRIPTION
    `Invoke-YDbCommand` executes a command against a database and returns a generic object for each row in the result
    set. Each object has a property for each column. If a column doesn't have a name, a generic `ColumnX` name is
    assigned, where `X` starts at 0 and increases by one for each nameless column.
 
    Pass the connection to the database to the `Connection` parameter. (Use the `Connect-YDatabase` function to create a
    connection.) Pass the command to run to the `Text` parameter. You may also pipe commands to `Invoke-YDbCommand`. If
    your command should be part of a transaction, pass the transaction to the `Transaction` parameter.
 
    If your command returns a single value, use the `-AsScalar` switch.
 
    If your command returns no results, use the `-NonQuery` switch. If your command affects any rows, the number of rows
    affected will be returned.
 
    To run a parameterized command, use `@name` parameters in your command and pass the values of those parameters in a
    hashtable to the `Parameter` parameter, e.g. `@{ '@name' = 'the_name' }`.
 
    To execute a stored procedure, set the `Text` parameter to the name of the stored procedure, set the `Type`
    parameter to `[Data.CommandType]::StoredProcedure`, and pass the procedure's parameters to the `Parameter` parameter
    (a hashtable of parameter names and values).
 
    Commands will time out after 30 seconds (the default .NET timeout). If you have a query that runs longer, pass the
    number of seconds to wait to the `Timeout` parameter.
 
    Command timings are output to the verbose stream, including the text of the command. Command parameters are not
    output. If you want to suppress sensitive commands from being output, set the `Verbose` parameter to `$false`, e.g.
    `-Verbose:$false`.
 
    Failed queries do not cause a terminating error. If you want your script to stop if your query fails, set the
    `ErrorAction` parameter to `Stop`, e.g. `-ErrorAction Stop`.
 
    .EXAMPLE
    Invoke-YDbCommand -Connection $conn -Text 'select * from MyTable'
 
    Demonstrates how to select rows from a table.
 
    .EXAMPLE
    'select 1','select 2' | Invoke-YDbCommand -Connection $conn
 
    Demonstrates that you can pipe commands to `Invoke-YDbCommand`.
 
    .EXAMPLE
    Invoke-YDbCommand -Connection $conn -Text 'select count(*) from MyTable' -AsScalar
 
    Demonstrates how to return a scalar value. If the command returns multiple rows/columns, returns the first row's
    first column's value.
 
    .EXAMPLE
    $rowsDeleted = Invoke-YDbCommand -Connection $conn -Text 'delete from dbo.Example' -NonQuery
 
    Demonstrates how to execute a command that doesn't return a value. If your command updates/deletes rows, the number
    of rows affected is returned.
 
    .EXAMPLE
    Invoke-YDbCommand -Connection $conn -Text 'insert into MyTable (Two,Three) values @Column2, @Column3' -Parameter @{ '@Column2' = 'Value2'; '@Column3' = 'Value3' } -NonQuery
 
    Demonstrates how to use parameterized queries.
 
    .EXAMPLE
    Invoke-YDbCommand -Connection $conn -Text 'sp_addrolemember -CommandType [Data.CommandType]::StoredProcedure -Parameter @{ '@rolename = 'db_owner'; '@membername' = 'myuser'; }
 
    Demonstrates how to execute a stored procedure, including how to pass its parameters using the `Parameter`
    parameter.
 
    .EXAMPLE
    Invoke-YDbCommand -Connection $conn -Text 'create login [yodeltest] with password ''P@$$w0rd''' -Verbose:$false
 
    Demonstrates how to prevent command timings for sensitive queries from being written to the verbose stream.
 
    .EXAMPLE
    Invoke-YDbCommand -Connection $conn -Text 'select * from a_really_involved_join_that_takes_a_long_time' -Timeout 120
 
    Demonstrates how to set the command timeout for commands that take longer than .NET's default timeout (30 seconds).
 
    .EXAMPLE
    Invoke-YDbCommand -Connection $conn -Text 'create table my_table (id int)' -Transaction $transaction
 
    Demonstrates that you can make the command part of a transaction by passing the transaction to the `Transaction`
    parameter.
    #>

    [CmdletBinding(DefaultParameterSetName='ExecuteReader')]
    param(
        # The connection to use.
        [Parameter(Mandatory, Position=0)]
        [Data.Common.DbConnection] $Connection,

        # The command to run/execute.
        [Parameter(Mandatory, Position=1, ValueFromPipeline)]
        [String] $Text,

        # The type of command being run. The default is `Text` for a SQL query.
        [Data.CommandType] $Type = [Data.CommandType]::Text,

        # The time (in seconds) to wait for a command to execute. The default is the .NET default, which is 30 seconds.
        [int] $Timeout,

        # Any parameters used in the command.
        [Parameter(Position=2)]
        [hashtable] $Parameter,

        # Return the result as a single value instead of a row. If the command returns multiple rows/columns, the value
        # of the first row's first column is returned.
        [Parameter(Mandatory,ParameterSetName='ExecuteScalar')]
        [switch] $AsScalar,

        # Executes a command that doesn't return any records. For updates/deletes, the number of rows affected will be
        # returned unless the NOCOUNT options is used.
        [Parameter(Mandatory,ParameterSetName='ExecuteNonQuery')]
        [switch] $NonQuery,

        # Any transaction the command should be part of.
        [Data.Common.DbTransaction] $Transaction
    )

    begin
    {
        Set-StrictMode -Version 'Latest'
        Use-CallerPreference -Cmdlet $PSCmdlet -SessionState $ExecutionContext.SessionState

        $verboseEnabled = $false
        if( (Write-Verbose 'Active' 4>&1) )
        {
            $verboseEnabled = $true
        }
    }

    process
    {
        $cmd = $Connection.CreateCommand()
        $cmd.CommandText = $Text
        $cmd.CommandTimeout = $Timeout
        $cmd.CommandType = $Type

        if( $Transaction )
        {
            $cmd.Transaction = $Transaction
        }

        if( $Parameter )
        {
            foreach( $name in $Parameter.Keys )
            {
                $value = $Parameter[$name]
                if( -not $name.StartsWith( '@' ) )
                {
                    $name = '@{0}' -f $name
                }
                [void]$cmd.Parameters.AddWithValue( $name, $value )
            }
        }

        $stopwatch = [Diagnostics.Stopwatch]::StartNew()
        try
        {
            if( $pscmdlet.ParameterSetName -like 'ExecuteNonQuery*' )
            {
                $rowsAffected = $cmd.ExecuteNonQuery()
                if( $rowsAffected -ge 0 )
                {
                    $rowsAffected
                }
            }
            elseif( $pscmdlet.ParameterSetName -like 'ExecuteScalar*' )
            {
                $cmd.ExecuteScalar()
            }
            else
            {
                $cmdReader = $cmd.ExecuteReader()
                try
                {
                    if( $cmdReader.HasRows )
                    {
                        while( $cmdReader.Read() )
                        {
                            $row = @{ }
                            for ($i= 0; $i -lt $cmdReader.FieldCount; $i++)
                            {
                                $name = $cmdReader.GetName( $i )
                                if( -not $name )
                                {
                                    $name = 'Column{0}' -f $i
                                }
                                $value = $cmdReader.GetValue($i)
                                if( $cmdReader.IsDBNull($i) )
                                {
                                    $value = $null
                                }
                                $row[$name] = $value
                            }
                            New-Object 'PsObject' -Property $row
                        }
                    }
                }
                finally
                {
                    $cmdReader.Close()
                }
            }
        }
        catch
        {
            # SQL Server exceptions can be brutally nested.
            $ex = $_.Exception
            while( $ex.InnerException )
            {
                $ex = $ex.InnerException
            }

            $errorMsg = '{0}{1}{2}' -f $_.Exception.Message,[Environment]::NewLine,$Text
            Write-Error -Message $errorMsg -Exception $ex -ErrorAction $ErrorActionPreference
        }
        finally
        {
            $lines = @()
            if ($verboseEnabled)
            {
                $lines = & {
                    if ($cmd.Parameters.Count)
                    {
                        $paramFieldLength =
                            $cmd.Parameters |
                            Select-Object -ExpandProperty 'ParameterName' |
                            Select-Object -ExpandProperty 'Length' |
                            Measure-Object -Maximum |
                            Select-Object -ExpandProperty 'Maximum'
                        for ($idx = 0 ; $idx -lt $cmd.Parameters.Count ; ++$idx)
                        {
                            $param = $cmd.Parameters[$idx]
                            $paramName = $param.ParameterName.PadRight($paramFieldLength)
                            $paramValue = $param.Value
                            "${paramName} ${paramValue}" | Write-Output
                        }
                    }

                    $Text -split '\r?\n' | Write-Output
                }
            }

            $cmd.Dispose()

            $stopwatch.Stop()

            # Only calculate and output timings if verbose output is enabled. We don't even call Write-Verbose because
            # some queries could have sensitive information in them, and in order to prevent them from being visible,
            # the user will add `-Verbose:$false` when calling this function. I'm being extra cautious here so there is
            # no way for someone to intercept sensitive queries.
            if ($verboseEnabled)
            {
                $duration = $stopwatch.Elapsed
                if( $duration.TotalHours -ge 1 )
                {
                    $durationDesc = '{0,2}h {1,3}m ' -f [int]$duration.TotalHours,$duration.Minutes
                }
                elseif( $duration.TotalMinutes -ge 1 )
                {
                    $durationDesc = '{0,2}m {1,3}s ' -f [int]$duration.TotalMinutes,$duration.Seconds
                }
                elseif( $duration.TotalSeconds -ge 1 )
                {
                    $durationDesc = '{0,2}s {1,3}ms' -f [int]$duration.TotalSeconds,$duration.Milliseconds
                }
                else
                {
                    $durationDesc = '{0,7}ms' -f [int]$duration.TotalMilliseconds
                }

                Write-Verbose -Message ('{0} {1}' -f $durationDesc, ($lines | Select-Object -First 1))
                foreach( $line in ($lines | Select-Object -Skip 1))
                {
                    Write-Verbose -Message ('{0} {1}' -f (' ' * $durationDesc.Length), $line)
                }
            }
        }
    }
}


# Copyright 2012 Aaron Jensen
#
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
#
# http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.

function Use-CallerPreference
{
    <#
    .SYNOPSIS
    Sets the PowerShell preference variables in a module's function based on the callers preferences.
 
    .DESCRIPTION
    Script module functions do not automatically inherit their caller's variables, including preferences set by common parameters. This means if you call a script with switches like `-Verbose` or `-WhatIf`, those that parameter don't get passed into any function that belongs to a module.
 
    When used in a module function, `Use-CallerPreference` will grab the value of these common parameters used by the function's caller:
 
     * ErrorAction
     * Debug
     * Confirm
     * InformationAction
     * Verbose
     * WarningAction
     * WhatIf
     
    This function should be used in a module's function to grab the caller's preference variables so the caller doesn't have to explicitly pass common parameters to the module function.
 
    This function is adapted from the [`Get-CallerPreference` function written by David Wyatt](https://gallery.technet.microsoft.com/scriptcenter/Inherit-Preference-82343b9d).
 
    There is currently a [bug in PowerShell](https://connect.microsoft.com/PowerShell/Feedback/Details/763621) that causes an error when `ErrorAction` is implicitly set to `Ignore`. If you use this function, you'll need to add explicit `-ErrorAction $ErrorActionPreference` to every function/cmdlet call in your function. Please vote up this issue so it can get fixed.
 
    .LINK
    about_Preference_Variables
 
    .LINK
    about_CommonParameters
 
    .LINK
    https://gallery.technet.microsoft.com/scriptcenter/Inherit-Preference-82343b9d
 
    .LINK
    http://powershell.org/wp/2014/01/13/getting-your-script-module-functions-to-inherit-preference-variables-from-the-caller/
 
    .EXAMPLE
    Use-CallerPreference -Cmdlet $PSCmdlet -SessionState $ExecutionContext.SessionState
 
    Demonstrates how to set the caller's common parameter preference variables in a module function.
    #>

    [CmdletBinding()]
    param (
        [Parameter(Mandatory = $true)]
        #[Management.Automation.PSScriptCmdlet]
        # The module function's `$PSCmdlet` object. Requires the function be decorated with the `[CmdletBinding()]` attribute.
        $Cmdlet,

        [Parameter(Mandatory = $true)]
        [Management.Automation.SessionState]
        # The module function's `$ExecutionContext.SessionState` object. Requires the function be decorated with the `[CmdletBinding()]` attribute.
        #
        # Used to set variables in its callers' scope, even if that caller is in a different script module.
        $SessionState
    )

    Set-StrictMode -Version 'Latest'

    # List of preference variables taken from the about_Preference_Variables and their common parameter name (taken from about_CommonParameters).
    $commonPreferences = @{
                              'ErrorActionPreference' = 'ErrorAction';
                              'DebugPreference' = 'Debug';
                              'ConfirmPreference' = 'Confirm';
                              'InformationPreference' = 'InformationAction';
                              'VerbosePreference' = 'Verbose';
                              'WarningPreference' = 'WarningAction';
                              'WhatIfPreference' = 'WhatIf';
                          }

    foreach( $prefName in $commonPreferences.Keys )
    {
        $parameterName = $commonPreferences[$prefName]

        # Don't do anything if the parameter was passed in.
        if( $Cmdlet.MyInvocation.BoundParameters.ContainsKey($parameterName) )
        {
            continue
        }

        $variable = $Cmdlet.SessionState.PSVariable.Get($prefName)
        # Don't do anything if caller didn't use a common parameter.
        if( -not $variable )
        {
            continue
        }

        if( $SessionState -eq $ExecutionContext.SessionState )
        {
            Set-Variable -Scope 1 -Name $variable.Name -Value $variable.Value -Force -Confirm:$false -WhatIf:$false
        }
        else
        {
            $SessionState.PSVariable.Set($variable.Name, $variable.Value)
        }
    }

}


function ConvertTo-YMsSqlIdentifier
{
    <#
    .SYNOPSIS
    Converts a string to a Microsoft SQL Server quoted identifier.
 
    .DESCRIPTION
    The `ConvertTo-YMsSqlIdentifier` function converts a string to a quoted identifier. Pipe the name to the function
    (or pass it to the `ImputObject` parameter). The function calls SQL Server's `QUOTENAME` function to quote and
    escape the name. The value is passed to the query as a parameter to avoid SQL injection attacks.
 
    Use this function if embedding object names in strings containing raw SQL.
 
    .EXAMPLE
    'hello[]world' | ConvertTo-YMsSqlIdentifier -Connection $conn
 
    Demonstrates how to pipe values to this function. In this example, the function calls
    `select quotename('hello[]world')`, which would return the string `[hello[]]world]`.
    #>

    [CmdletBinding()]
    param(
        # The connection to Microsoft SQL Server.
        [Parameter(Mandatory)]
        [Data.Common.DbConnection] $Connection,

        # The value to convert.
        [Parameter(Mandatory, ValueFromPipeline)]
        [String] $InputObject
    )

    process
    {
        return Invoke-YMsSqlCommand -Connection $Connection `
                                    -Text 'select QUOTENAME(@name)' `
                                    -Parameter @{ '@name' = $InputObject } `
                                    -AsScalar
    }
}


function Get-YMsSqlExtendedProperty
{
    <#
    .SYNOPSIS
    Gets Microsoft SQL Server extended properties.
 
    .DESCRIPTION
    The `Get-YMsSqlExtendedProperty` function gets extended property metadata for objects in a Microsoft SQL Server. It
    calls the `fn_listextendedproperty` to get the extended properties. To get all extended properties for arbitrary
    objects, pass appropriate values level 0, 1, and types and names to the `Level0Type`, `Level0Name`, `Level1Type`,
    `Level1Name`, `Level2Type`, and `Level2Name` parameters. To get a specific extended property, pass its name to the
    `Name` parameter.
 
    To get extended properties for a schema, pass the schema's name to the `SchemaName` parameter.
 
    To get extended properties for a table, pass the table's name and schema name to the `TableName` and `SchemaName`
    parameters, respectively.
 
    To get extended properties for a column on a table, pass the column's name, table name, and table's schema name to
    the `ColumnName`, `TableName`, and `SchemaName` parameters, respectively.
 
    To use `NULL` as the value for any of the `fn_listextendedproperty` level or name parameters, omit the argument or
    pass `$null`.
 
    To use `default` as the value for any of the `fn_listextendedproperty` level or name parameters, pass
    `[Yodel_MsSql_QueryKeyword]::Default`.
 
    .EXAMPLE
    Get-YMsSqlExtendedProperty -Connection $conn -SchemaName 'yodel'
 
    Demonstrates how to get a schema's extended properties by passing the schema name to the `SchemaName` parameter.
 
    .EXAMPLE
    Get-YMsSqlExtendedProperty -Connection $conn -SchemaName 'yodel' -TableName 'Table_1'
 
    Demonstrates how to get a table's extended properties by passing the table name to the `TableName` parameter and the
    table's schema to the `SchemaName` parameter.
 
    .EXAMPLE
    Get-YMsSqlExtendedProperty -Connection $conn -SchemaName 'yodel' -TableName 'Table_1' -ColumnName 'id
 
    Demonstrates how to get a table column's extended properties by passing the column name to the `ColumnName`
    parameter and the columns's table to the `TableName` parameter and the table's schema to the `SchemaName` parameter.
    #>

    [CmdletBinding(DefaultParameterSetName='Raw')]
    param(
        # The connection to Microsoft SQL Server.
        [Parameter(Mandatory)]
        [Data.Common.DbConnection] $Connection,

        # The schema name whose extended properties to get. Or the schema name of the table whose extended properties to
        # get. Defaults to `NULL`.
        [Parameter(Mandatory, ParameterSetName='ForSchema')]
        [Parameter(ParameterSetName='ForTable')]
        [Parameter(ParameterSetName='ForTableColumn')]
        [String] $SchemaName,

        # The table name whose extended properties to get, or the table name of the column whose extended properties to
        # get. Default is `NULL`.
        [Parameter(Mandatory, ParameterSetName='ForTable')]
        [Parameter(Mandatory, ParameterSetName='ForTableColumn')]
        [String] $TableName,

        # The column name whose extended properties to get. Default is `NULL`.
        [Parameter(Mandatory, ParameterSetName='ForTableColumn')]
        [String] $ColumnName,

        # The name of the extended property to get. Defalt is `NULL`. To use `default` as the value, pass
        # `[Yodel_MsSql_QueryKeyword]::Default`.
        [Object] $Name,

        # The value for the `fn_listextendedproperty` function's `level0_object_type` parameter. Default is `NULL`. To
        # use `default` as the value, pass `[Yodel_MsSql_QueryKeyword]::Default`.
        [Parameter(Mandatory, ParameterSetName='RawL0')]
        [Parameter(Mandatory, ParameterSetName='RawL1')]
        [Parameter(Mandatory, ParameterSetName='RawL2')]
        [AllowNull()]
        [Object] $Level0Type,

        # The value for the `fn_listextendedproperty` function's `level0_object_name` parameter. Default is `NULL`. To
        # use `default` as the value, pass `[Yodel_MsSql_QueryKeyword]::Default`.
        [Parameter(Mandatory, ParameterSetName='RawL0')]
        [Parameter(Mandatory, ParameterSetName='RawL1')]
        [Parameter(Mandatory, ParameterSetName='RawL2')]
        [AllowNull()]
        [Object] $Level0Name,

        # The value for the `fn_listextendedproperty` function's `level1_object_type` parameter. Default is `NULL`. To
        # use `default` as the value, pass `[Yodel_MsSql_QueryKeyword]::Default`.
        [Parameter(Mandatory, ParameterSetName='RawL1')]
        [Parameter(Mandatory, ParameterSetName='RawL2')]
        [AllowNull()]
        [Object] $Level1Type,

        # The value for the `fn_listextendedproperty` function's `level1_object_name` parameter. Default is `NULL`. To
        # use `default` as the value, pass `[Yodel_MsSql_QueryKeyword]::Default`.
        [Parameter(Mandatory, ParameterSetName='RawL1')]
        [Parameter(Mandatory, ParameterSetName='RawL2')]
        [AllowNull()]
        [Object] $Level1Name,

        # The value for the `fn_listextendedproperty` function's `level2_object_type` parameter. Default is `NULL`. To
        # use `default` as the value, pass `[Yodel_MsSql_QueryKeyword]::Default`.
        [Parameter(Mandatory, ParameterSetName='RawL2')]
        [AllowNull()]
        [Object] $Level2Type,

        # The value for the `fn_listextendedproperty` function's `level2_object_name` parameter. Default is `NULL`. To
        # use `default` as the value, pass `[Yodel_MsSql_QueryKeyword]::Default`.
        [Parameter(Mandatory, ParameterSetName='RawL2')]
        [AllowNull()]
        [Object] $Level2Name
    )

    Set-StrictMode -Version 'Latest'
    Use-CallerPreference -Cmdlet $PSCmdlet -SessionState $ExecutionContext.SessionState

    if ($PSCmdlet.ParameterSetName -notlike 'Raw*')
    {
        if (-not $SchemaName)
        {
            $SchemaName = 'dbo'
        }

        $l1Type = $l1Name = $l2Type = $l2Name = $null

        if ($PSCmdlet.ParameterSetName -ne 'ForSchema')
        {
            $l1Type = 'table'
            $l1Name = $TableName

            $l2Type = $null
            $l2Name = $null
            if ($PSBoundParameters.ContainsKey('ColumnName'))
            {
                $l2Type = 'column'
                $l2Name = $ColumnName
            }
        }

        return Get-YMsSqlExtendedProperty -Connection $Connection `
                                         -Name $Name `
                                         -Level0Type 'schema' `
                                         -Level0Name $SchemaName `
                                         -Level1Type $l1Type `
                                         -Level1Name $l1Name `
                                         -Level2Type $l2Type `
                                         -Level2Name $l2Name
    }

    $parameter = @{}
    function Get-ArgValue
    {
        [CmdletBinding()]
        param(
            [Parameter(Mandatory, ValueFromPipeline)]
            [AllowNull()]
            [Object] $InputObject
        )

        if ($null -eq $InputObject)
        {
            return 'NULL'
        }

        if ($InputObject -eq [Yodel_MsSql_QueryKeyword]::Default)
        {
            return 'default'
        }

        $paramName = "@param$($parameter.Count)"
        $parameter[$paramName] = $InputObject
        return $paramName
    }

    $nameArg = $Name | Get-ArgValue
    $l0TypeArg = $Level0Type | Get-ArgValue
    $l0NameArg = $Level0Name | Get-ArgValue
    $l1TypeArg = $Level1Type | Get-ArgValue
    $l1NameArg = $Level1Name | Get-ArgValue
    $l2TypeArg = $Level2Type | Get-ArgValue
    $l2NameArg = $Level2Name | Get-ArgValue

    $query = "select * from sys.fn_listextendedproperty(${nameArg}, ${l0TypeArg}, ${l0NameArg}, ${l1TypeArg}, ${l1NameArg}, ${l2TypeArg}, ${l2NameArg})"
    $result = Invoke-YMsSqlCommand -Connection $Connection -Text $query -Parameter $parameter

    if ($result)
    {
        return $result
    }

    $msg = 'There are no extended properties on '

    if ($PSCmdlet.ParameterSetName -eq 'Raw')
    {
        $msg = "${msg}database ""$($Connection.Database)"""
    }

    function Get-LevelMessage
    {
        param(
            [Parameter(Mandatory)]
            [AllowNull()]
            [Object] $Type,

            [Parameter(Mandatory)]
            [AllowNull()]
            [Object] $Name
        )

        if ($null -eq $Type -or $Type -eq [Yodel_MsSql_QueryKeyword]::Default)
        {
            return ''
        }

        if ($null -eq $Name -or $Name -eq [Yodel_MsSql_QueryKeyword]::Default)
        {
            return "all ${Type}"
        }

        return "${Type} ""${Name}"""
    }

    $seperator = ''
    if ($PSCmdlet.ParameterSetName -in @('RawL0', 'RawL1', 'RawL2'))
    {
        $levelMsg = Get-LevelMessage -Type $Level0Type -Name $Level0Name
        $msg = "${msg}${levelMsg}"
        if ($levelMsg)
        {
            $seperator = ', '
        }
    }

    if ($PSCmdlet.ParameterSetName -in @('RawL1', 'RawL2'))
    {
        $levelMsg = Get-LevelMessage -Type $Level1Type -Name $Level1Name
        if ($levelMsg)
        {
            $msg = "${msg}${seperator}${levelMsg}"
            $seperator = ', '
        }
    }

    if ($PSCmdlet.ParameterSetName -eq 'RawL2')
    {
        $levelMsg = Get-LevelMessage -Type $Level2Type -Name $Level2Name
        if ($levelMsg)
        {
            $msg = "${msg}${seperator}${levelMsg}"
        }
    }

    Write-Error -Message "${msg}." -ErrorAction $ErrorActionPreference
}



function Get-YMsSqlSchema
{
    <#
    .SYNOPSIS
    Gets schema metadata from Microsoft SQL Server.
 
    .DESCRIPTION
    The `Get-YMsSqlSchema` function gets schema metadata from Microsoft SQL Server. Pass the connection to SQL Server to
    the `Connection` parameter and the name of the schema to the `Name` parameter. The function returns all columns
    from `sys.schemas` for the given schema.
 
    .EXAMPLE
    Get-YMsSqlSchema -Connection $conn -Name 'dbo'
 
    Demonstrates how to use this function. In this example, all columns from the `sys.schemas` table for the `dbo`
    schema will be returned.
    #>

    [CmdletBinding()]
    param(
        # The connection to Microsoft SQL Server.
        [Parameter(Mandatory)]
        [Data.Common.DbConnection] $Connection,

        # The schema name.
        [Parameter(Mandatory)]
        [String] $Name
    )

    Set-StrictMode -Version 'Latest'
    Use-CallerPreference -Cmdlet $PSCmdlet -SessionState $ExecutionContext.SessionState

    $query = 'select * from sys.schemas where name = @schemaName'
    $result = Invoke-YMsSqlCommand -Connection $Connection -Text $query -Parameter @{ '@schemaName' = $Name }
    if (-not $result)
    {
        $msg = "Schema ""${Name}"" does not exist."
        Write-Error $msg -ErrorAction $ErrorActionPreference
        return
    }

    return $result
}


function Get-YMsSqlTable
{
    <#
    .SYNOPSIS
    Gets metadata about a table from Microsoft SQL Server.
 
    .DESCRIPTION
    the `Get-YMsSqlTable` function gets metadata about a table from Microsoft SQL Server. Pass the connection to SQL
    Server to the `Connection` parameter and the table name to the `Name` parameter. The function returns the result of
    select all the columns for that table from `sys.tables`.
 
    If the table isn't in the `dbo` schema, pass its schema name to the `SchemaName` parameter.
 
    .EXAMPLE
    Get-YMsSqlTable -Connection $conn -Name 'Table_1'
 
    Demonstrates how to get metadata for a table in the `dbo` schema. In this example, returns the `[dbo].[Table_1]`
    table's record from `sys.tables`.
 
    .EXAMPLE
    Get-YMsSqlTable -Connection $conn -SchemaName 'yodel' -Name 'Table_1'
 
    Demonstrates how to get metadata for a table in a custom schema. In this example, returns the `[yodel].[Table_1]`
    table's record from `sys.tables`.
    #>

    [CmdletBinding()]
    param(
        # The connection to Microsoft SQL Server.
        [Parameter(Mandatory)]
        [Data.Common.DbConnection] $Connection,

        # The table's schema. Defaults to `dbo`.
        [String] $SchemaName = 'dbo',

        # The table name.
        [Parameter(Mandatory)]
        [String] $Name
    )

    Set-StrictMode -Version 'Latest'
    Use-CallerPreference -Cmdlet $PSCmdlet -SessionState $ExecutionContext.SessionState

    $query = @"
select
    t.*
from
    sys.tables t
        join
    sys.schemas s
            on t.schema_id=s.schema_id
where
    t.name=@name and s.name=@schemaName
"@

    $queryArgs = @{
        '@name' = $Name;
        '@schemaName' = $SchemaName;
    }
    $result = Invoke-YMsSqlCommand -Connection $Connection -Text $query -Parameter $queryArgs
    if (-not $result)
    {
        $msg = "Table [${SchemaName}].[${Name}] does not exist."
        Write-Error -Message $msg -ErrorAction $ErrorActionPreference
        return
    }

    return $result
}


function Initialize-YMsSqlDatabase
{
    <#
    .SYNOPSIS
    Ensures a Microsoft SQL Server database exists.
 
    .DESCRIPTION
    The `Initialize-YMsSqlDatabase` function creates a database in Microsoft SQL Server if it doesn't already exist.
    Pass the connection to SQL Server to the `Connection` parameter and the name of the database to the `Name`
    parameter. If the database doesn't exist (i.e. there's no record for it in `sys.databases`), it is created. If the
    function completes without writing or throwing an error, the database will exist.
 
    .EXAMPLE
    Initialize-YMsSqlDatabase -Connection $conn -Name 'Yodel'
 
    Demonstrates how to use this function to ensure a database exists. In this example, `Initialize-YMsSqlDatabase` will
    create the `Yodel` database if and only if the `Yodel` database doesn't exist.
    #>

    [CmdletBinding()]
    param(
        # The connection to Microsoft SQL Server.
        [Parameter(Mandatory)]
        [Data.Common.DbConnection] $Connection,

        # The database name.
        [Parameter(Mandatory)]
        [String] $Name
    )

    Set-StrictMode -Version 'Latest'
    Use-CallerPreference -Cmdlet $PSCmdlet -SessionState $ExecutionContext.SessionState

    $query = 'select 1 from sys.databases where name=@name'
    $dbExists = Invoke-YMsSqlCommand -Connection $Connection -Text $query -Parameter @{ '@name' = $Name } -AsScalar
    if ($dbExists)
    {
        return
    }

    $Name = $Name | ConvertTo-YMsSqlIdentifier -Connection $Connection
    $query = "create database ${Name}"
    Invoke-YMsSqlCommand -Connection $Connection -Text $query -NonQuery
}


function Initialize-YMsSqlSchema
{
    <#
    .SYNOPSIS
    Ensures a schema exists in a Microsoft SQL Server database.
 
    .DESCRIPTION
    The `Initialize-YMsSqlSchema` function creates a schema in a SQL Server database if that schema doesn't already
    exist. Pass the connection to the SQL server to use to the `Connection` parameter and the name of the schema to
    the `Name` parameter.
 
    .EXAMPLE
    Initialize-YMsSqlSchema -Connection $conn -Name 'Yodel'
 
    Demonstrates how to use this function. In this example, the `Yodel` schema will be created, but only if it doesn't
    already exist.
    #>

    [CmdletBinding()]
    param(
        # The connection to Microsoft SQL Server.
        [Parameter(Mandatory)]
        [Data.Common.DbConnection] $Connection,

        # The schema's name.
        [Parameter(Mandatory)]
        [String] $Name
    )

    Set-StrictMode -Version 'Latest'
    Use-CallerPreference -Cmdlet $PSCmdlet -SessionState $ExecutionContext.SessionState

    if (Test-YMsSqlSchema -Connection $Connection -Name $Name)
    {
        return
    }

    $Name = $Name | ConvertTo-YMsSqlIdentifier -Connection $Connection
    $query = "create schema ${Name}"
    Invoke-YMsSqlCommand -Connection $Connection -Text $query -NonQuery
}


function Invoke-YMsSqlCommand
{
    <#
    .SYNOPSIS
    Uses ADO.NET to execute a query in a SQL Server database.
 
    .DESCRIPTION
    `Invoke-YMsSqlCommand` executes a SQL query against a SQL Server database. The function opens a connection to SQL
    Server, executes the query, then closes the connection. Pass the name of the SQL Server (hostname and instance name)
    to the `SqlServerName` parameter. Pass the database name to the `DatabaseName` parameter. By default, the query is
    run as the current user. To run as a custom user, pass the user's credentials to the `Credential` parameter.
 
    Pass the query to run to the `Text` parameter. You may also pipe queries to `Invoke-YMsSqlCommand`. Piped queries
    are all run using the same connection.
 
    The function returns a generic object for each row in the result set. Each object has a property for each column. If
    a column doesn't have a name, a generic `ColumnX` name is assigned, where `X` starts at 0 and increases by one for
    each nameless column.
 
    If your query returns a single value, use the `-AsScalar` switch.
 
    If your query returns no results, use the `-NonQuery` switch. If your query affects any rows, the number of rows
    affected will be returned.
 
    To run a parameterized query, use `@name` parameters in your query and pass the values of those parameters in a
    hashtable to the `Parameter` parameter, e.g. `@{ '@name' = 'the_name' }`.
 
    To execute a stored procedure, set the `Text` parameter to the name of the stored procedure, set the `Type`
    parameter to `[Data.CommandType]::StoredProcedure`, and pass the procedure's parameters to the `Parameter` parameter
    (a hashtable of parameter names and values).
 
    Queries will time out after 30 seconds (the default .NET timeout). If you have a query that runs longer, pass the
    number of seconds to wait to the `Timeout` parameter.
 
    Query timings are output to the verbose stream, including the text of the query. If you want to suppress sensitive
    queries from being output, set the `Verbose` parameter to `$false`, e.g. `-Verbose:$false`.
 
    The `Invoke-YMsSqlCommand` function constructs a connection string for you based on the values of the
    `SqlServerName` and `DatabaseName` parameters. If you have custom properties you'd like added to the connection
    string, pass them to the `ConnectionString` parameter.
 
    Failed queries do not cause a terminating error. If you want your script to stop if your query fails, set the
    `ErrorAction` parameter to `Stop`, e.g. `-ErrorAction Stop`.
 
    .EXAMPLE
    Invoke-YMsSqlCommand -SqlServerName '.' -DatabaseName 'master' -Text 'select * from MyTable'
 
    Demonstrates how to select rows from a table.
 
    .EXAMPLE
    'select 1','select 2' | Invoke-YMsSqlCommand -SqlServerName '.' -DatabaseName 'master'
 
    Demonstrates that you can pipe commands to `Invoke-YMsSqlCommand`. All queries piped to `Invoke-YMsSqlCommand` are
    run using the same connection.
 
    .EXAMPLE
    Invoke-YMsSqlCommand -SqlServerName '.' -DatabaseName 'master' -Text 'select count(*) from MyTable' -AsScalar
 
    Demonstrates how to return a scalar value. If the command returns multiple rows/columns, returns the first row's
    first column's value.
 
    .EXAMPLE
    $rowsDeleted = Invoke-YMsSqlCommand -SqlServerName '.' -DatabaseName 'master' -Text 'delete from dbo.Example' -NonQuery
 
    Demonstrates how to execute a command that doesn't return a value. If your command updates/deletes rows, the number
    of rows affected is returned.
 
    .EXAMPLE
    Invoke-YMsSqlCommand -SqlServerName '.' -DatabaseName 'master' -Text 'insert into MyTable (Two,Three) values @Column2, @Column3' -Parameter @{ '@Column2' = 'Value2'; '@Column3' = 'Value3' } -NonQuery
 
    Demonstrates how to use parameterized queries.
 
    .EXAMPLE
    Invoke-YMsSqlCommand -SqlServerName '.' -DatabaseName 'master' -Text 'sp_addrolemember' -CommandType [Data.CommandType]::StoredProcedure -Parameter @{ '@rolename' = 'db_owner'; '@membername' = 'myuser'; }
 
    Demonstrates how to execute a stored procedure, including how to pass its parameters using the `Parameter`
    parameter.
 
    .EXAMPLE
    Invoke-YMsSqlCommand -SqlServerName '.' -DatabaseName 'master' -Text 'create login [yodeltest] with password ''P@$$w0rd''' -Verbose:$false
 
    Demonstrates how to prevent command timings for sensitive queries from being written to the verbose stream.
 
    .EXAMPLE
    Invoke-YMsSqlCommand -SqlServerName '.' -DatabaseName 'master' -Text 'select * from a_really_involved_join_that_takes_a_long_time' -Timeout 120
 
    Demonstrates how to set the command timeout for commands that take longer than .NET's default timeout (30 seconds).
    #>

    [CmdletBinding(DefaultParameterSetName='AdHoc_ExecuteReader')]
    param(
        # The database connection to use.
        [Parameter(Mandatory, ParameterSetName='Connection_ExecuteNonQuery')]
        [Parameter(Mandatory, ParameterSetName='Connection_ExecuteReader')]
        [Parameter(Mandatory, ParameterSetName='Connection_ExecuteScalar')]
        [Data.Common.DbConnection] $Connection,

        # The SQL Server instance to connect to.
        [Parameter(Mandatory, Position=0, ParameterSetName='AdHoc_ExecuteNonQuery')]
        [Parameter(Mandatory, Position=0, ParameterSetName='AdHoc_ExecuteReader')]
        [Parameter(Mandatory, Position=0, ParameterSetName='AdHoc_ExecuteScalar')]
        [String] $SqlServerName,

        # The database to connect to.
        [Parameter(Mandatory, Position=1, ParameterSetName='AdHoc_ExecuteNonQuery')]
        [Parameter(Mandatory, Position=1, ParameterSetName='AdHoc_ExecuteReader')]
        [Parameter(Mandatory, Position=1, ParameterSetName='AdHoc_ExecuteScalar')]
        [String] $DatabaseName,

        [Parameter(ParameterSetName='AdHoc_ExecuteNonQuery')]
        [Parameter(ParameterSetName='AdHoc_ExecuteReader')]
        [Parameter(ParameterSetName='AdHoc_ExecuteScalar')]
        [ValidateNotNullOrEmpty()]
        [pscredential] $Credential,

        # The connection string to use.
        [Parameter(ParameterSetName='AdHoc_ExecuteNonQuery')]
        [Parameter(ParameterSetName='AdHoc_ExecuteReader')]
        [Parameter(ParameterSetName='AdHoc_ExecuteScalar')]
        [String] $ConnectionString,

        # The command to run/execute.
        [Parameter(Mandatory, Position=2, ValueFromPipeline)]
        [String] $Text,

        # Any parameters used in the command.
        [hashtable] $Parameter,

        # Return the result as a single value instead of a row. If the command returns multiple rows/columns, the value
        # of the first row's first column is returned.
        [Parameter(Mandatory, ParameterSetName='AdHoc_ExecuteScalar')]
        [Parameter(Mandatory, ParameterSetName='Connection_ExecuteScalar')]
        [switch] $AsScalar,

        # Executes a command that doesn't return any records. For updates/deletes, the number of rows affected will be
        # returned unless the NOCOUNT option is used.
        [Parameter(Mandatory, ParameterSetName='AdHoc_ExecuteNonQuery')]
        [Parameter(Mandatory, ParameterSetName='Connection_ExecuteNonQuery')]
        [switch] $NonQuery,

        # The time (in seconds) to wait for a command to execute. The default is .NET's default timeout, which is 30
        # seconds.
        [int] $Timeout,

        # The type of command being run. The default is Text, or a plain query.
        [Data.CommandType] $Type = [Data.CommandType]::Text
    )

    begin
    {
        Set-StrictMode -Version 'Latest'
        Use-CallerPreference -Cmdlet $PSCmdlet -SessionState $ExecutionContext.SessionState

        $isMyConn = $false
        if (-not $Connection)
        {
            $optionalParams = @{}
            if( $Credential )
            {
                $optionalParams['Credential'] = $Credential
            }

            if( $ConnectionString )
            {
                $optionalParams['ConnectionString'] = $ConnectionString
            }

            $connection = Connect-YDatabase -SqlServerName $SqlServerName -DatabaseName $DatabaseName @optionalParams
            $isMyConn = $true
        }

        $optionalParams = @{}

        if( $AsScalar )
        {
            $optionalParams['AsScalar'] = $true
        }

        if( $NonQuery )
        {
            $optionalParams['NonQuery'] = $true
        }

        if( $Timeout )
        {
            $optionalParams['Timeout'] = $Timeout
        }

        if( $Type )
        {
            $optionalParams['Type'] = $Type
        }

        if( $Parameter )
        {
            $optionalParams['Parameter'] = $Parameter
        }
    }

    process
    {
        $cmdFailed = $true
        try
        {
            Invoke-YDbCommand -Connection $Connection -Text $Text @optionalParams
            $cmdFailed = $false
        }
        finally
        {
            # Terminating errors stop the pipeline.
            if( $cmdFailed )
            {
                if ($isMyConn)
                {
                    $Connection.Close()
                }
            }
        }
    }

    end
    {
        if ($isMyConn)
        {
            $connection.Close()
        }
    }
}

function Invoke-YSqlServerCommand
{
    <#
    .SYNOPSIS
    ***OBSOLETE.*** Use `Invoke-YMsSqlCommand` instead.
 
    .DESCRIPTION
    ***OBSOLETE.*** Use `Invoke-YMsSqlCommand` instead.
 
    .EXAMPLE
    ***OBSOLETE.*** Use `Invoke-YMsSqlCommand` instead.
    #>

    [CmdletBinding(DefaultParameterSetName='ExecuteReader')]
    param(
        [Data.Common.DbConnection] $Connection,

        [Parameter(Mandatory, Position=0)]
        [String] $SqlServerName,

        [Parameter(Mandatory, Position=1)]
        [String] $DatabaseName,

        [ValidateNotNullOrEmpty()]
        [pscredential] $Credential,

        [String] $ConnectionString,

        [Parameter(Mandatory, Position=2, ValueFromPipeline)]
        [String] $Text,

        [hashtable] $Parameter,

        [Parameter(Mandatory, ParameterSetName='ExecuteScalar')]
        [switch] $AsScalar,

        [Parameter(Mandatory, ParameterSetName='ExecuteNonQuery')]
        [switch] $NonQuery,

        [int] $Timeout,

        [Data.CommandType] $Type = [Data.CommandType]::Text
    )

    process
    {
        $msg = 'Invoke-YMsSqlCommand is obsolete and will be removed in the next major version of Yodel. Please use ' +
            'Invoke-YMsSqlCommand instead.'
        Write-Warning -Message $msg

        Invoke-YMsSqlCommand @PSBoundParameters
    }
}



function Remove-YMsSqlTable
{
    <#
    .SYNOPSIS
    Drops a table from Microsoft SQL Server.
 
    .DESCRIPTION
    The `Remove-YMsSqlTable` function drops a table in SQL Server. Pass the connection to SQL Server to the `Connection`
    parameter and the table name to the `Name` parameter. If the table doesn't exist, the function writes an error.
 
    If the table isn't in the `dbo` schema, pass its schema to the `SchemaName` parameter.
 
    .EXAMPLE
    Remove-YMsSqlTable -Connection $conn -Name 'Table_2'
 
    Demonstrates how to remove the `[dbo].[Table_2]` table.
 
    .EXAMPLE
    Remove-YMsSqlTable -Connection $conn -SchemaName 'yodel' -Name 'Table_2'
 
    Demonstrates how to remove the `[yodel].[Table_2]` table.
    #>

    [CmdletBinding()]
    param(
        # The connection to Microsoft SQL Server.
        [Parameter(Mandatory)]
        [Data.Common.DbConnection] $Connection,

        # The table's schema name. Defaults to `dbo`.
        [String] $SchemaName = 'dbo',

        # The table's name.
        [Parameter(Mandatory)]
        [String] $Name
    )

    Set-StrictMode -Version 'Latest'
    Use-CallerPreference -Cmdlet $PSCmdlet -SessionState $ExecutionContext.SessionState

    if (-not (Test-YMsSqlTable -Connection $Connection -SchemaName $SchemaName -Name $Name))
    {
        $msg = "Table ${SchemaName}.${Name} does not exist."
        Write-Error -Message $msg -ErrorAction $ErrorActionPreference
        return
    }

    $SchemaName, $Name = $SchemaName, $Name | ConvertTo-YMsSqlIdentifier -Connection $Connection
    $query = "drop table ${SchemaName}.${Name}"
    Invoke-YMsSqlCommand -Connection $Connection -Text $query -NonQuery
}


function Set-YMsSqlExtendedProperty
{
    <#
    .SYNOPSIS
    Adds or updates Microsoft SQL Server extended properties.
 
    .DESCRIPTION
    The `Set-YMsSqlExtendedProperty` function adds or updates extended property metadata for objects in SQL Server. It
    calls the `fn_addextendedproperty` to add a new extended property and `fn_updateextendedproperty` to update an
    existing extended property. An extended property is updated only if its value has changed. Pass the connection to
    Microsoft SQL Server to the connection parameter, the name of the extended property to the `Name` parameter and the
    value to the `Value` property.
 
    To set an extended property for an arbitrary object, pass the appropriate level 0, 1, and 2 types and names for that
    object to the `Level0Type`, `Level0Name`, `Level1Type`, `Level1Name`, `Level2Type`, and `Level2Name` parameters. The
    value `$null` is allowed for any level type/name parameter.
 
    To set an extended property for a schema, pass the schema's name to the `SchemaName` parameter.
 
    To set an extended property for a table, pass the table's name and schema name to the `TableName` and `SchemaName`
    parameters, respectively.
 
    To set an extended property for a column on a table, pass the column's name, table name, and table's schema name to
    the `ColumnName`, `TableName`, and `SchemaName` parameters, respectively.
 
    .EXAMPLE
    Set-YMsSqlExtendedProperty -Connection $conn -SchemaName 'yodel' -Name 'Yodel_Example' -Value '1'
 
    Demonstrates how to set a schema extended property by passing the schema name to the `SchemaName` parameter.
 
    .EXAMPLE
    Set-YMsSqlExtendedProperty -Connection $conn -SchemaName 'yodel' -TableName 'Table_1' -Name 'Yodel_Example' -Value '2'
 
    Demonstrates how to set a table extended properties by passing the table name to the `TableName` parameter and the
    table's schema to the `SchemaName` parameter.
 
    .EXAMPLE
    Set-YMsSqlExtendedProperty -Connection $conn -SchemaName 'yodel' -TableName 'Table_1' -ColumnName 'id' -Name 'Yodel_Example' -Value '3'
 
    Demonstrates how to set a table column extended property by passing the column name to the `ColumnName` parameter,
    the columns's table name to the `TableName` parameter and the table's schema name to the `SchemaName` parameter.
    #>

    [CmdletBinding(DefaultParameterSetName='Raw')]
    param(
        # The connection to Microsoft SQL Server.
        [Parameter(Mandatory)]
        [Data.Common.DbConnection] $Connection,

        # The schema name whose extended property to set. Or the schema name of the table whose extended propert to
        # set. Defaults to `NULL`.
        [Parameter(Mandatory, ParameterSetName='ForSchema')]
        [Parameter(ParameterSetName='ForTable')]
        [Parameter(ParameterSetName='ForTableColumn')]
        [String] $SchemaName,

        # The table name whose extended property to set, or the table name of the column whose extended property to
        # set. Default is `NULL`.
        [Parameter(Mandatory, ParameterSetName='ForTable')]
        [Parameter(Mandatory, ParameterSetName='ForTableColumn')]
        [String] $TableName,

        # The column name whose extended property to set. Default is `NULL`.
        [Parameter(Mandatory, ParameterSetName='ForTableColumn')]
        [String] $ColumnName,

        # The name of the extended property to set. Defalt is `NULL`.
        [Parameter(Mandatory)]
        [String] $Name,

        # The name of the extended property to set. Defalt is `NULL`.
        [Parameter(Mandatory)]
        [AllowEmptyString()]
        [AllowNull()]
        [String] $Value,

        # The value for the `fn_addextendedproperty` or `fn_updateextededproperty` function's `level0_object_type`
        # parameter. Default is `NULL`.
        [Parameter(Mandatory, ParameterSetName='RawL0')]
        [Parameter(Mandatory, ParameterSetName='RawL1')]
        [Parameter(Mandatory, ParameterSetName='RawL2')]
        [AllowNull()]
        [Object] $Level0Type,

        # The value for the `fn_addextendedproperty` or `fn_updateextededproperty` function's `level0_object_name`
        # parameter. Default is `NULL`.
        [Parameter(Mandatory, ParameterSetName='RawL0')]
        [Parameter(Mandatory, ParameterSetName='RawL1')]
        [Parameter(Mandatory, ParameterSetName='RawL2')]
        [AllowNull()]
        [Object] $Level0Name,

        # The value for the `fn_addextendedproperty` or `fn_updateextededproperty` function's `level1_object_type`
        # parameter. Default is `NULL`.
        [Parameter(Mandatory, ParameterSetName='RawL1')]
        [Parameter(Mandatory, ParameterSetName='RawL2')]
        [AllowNull()]
        [Object] $Level1Type,

        # The value for the `fn_addextendedproperty` or `fn_updateextededproperty` function's `level1_object_name`
        # parameter. Default is `NULL`.
        [Parameter(Mandatory, ParameterSetName='RawL1')]
        [Parameter(Mandatory, ParameterSetName='RawL2')]
        [AllowNull()]
        [Object] $Level1Name,

        # The value for the `fn_addextendedproperty` or `fn_updateextededproperty` function's `level2_object_type`
        # parameter. Default is `NULL`.
        [Parameter(Mandatory, ParameterSetName='RawL2')]
        [AllowNull()]
        [Object] $Level2Type,

        # The value for the `fn_addextendedproperty` or `fn_updateextededproperty` function's `level2_object_name`
        # parameter. Default is `NULL`.
        [Parameter(Mandatory, ParameterSetName='RawL2')]
        [AllowNull()]
        [Object] $Level2Name
    )

    Set-StrictMode -Version 'Latest'
    Use-CallerPreference -Cmdlet $PSCmdlet -SessionState $ExecutionContext.SessionState

    if ($PSCmdlet.ParameterSetName -notlike 'Raw*')
    {
        if (-not $SchemaName)
        {
            $SchemaName = 'dbo'
        }

        $setArgs = @{
            Connection = $Connection;
            Name = $Name;
            Value = $Value;
            Level0Type = 'schema';
            Level0Name = $SchemaName;
        }

        if ($PSCmdlet.ParameterSetName -ne 'ForSchema')
        {
            $setArgs['Level1Type'] = 'table'
            $setArgs['Level1Name'] = $TableName

            if ($PSBoundParameters.ContainsKey('ColumnName'))
            {
                $setArgs['Level2Type'] = 'column'
                $setArgs['Level2Name'] = $ColumnName
            }
        }

        return Set-YMsSqlExtendedProperty @setArgs
    }

    $propArgs = @{}
    foreach ($argPosition in '0', '1', '2')
    {
        foreach ($argKind in @('Type', 'Name'))
        {
            $argName = "Level${argPosition}${argKind}"
            if (-not $PSBoundParameters.ContainsKey($argName))
            {
                continue
            }
            $propArgs[$argName] = $PSBoundParameters[$argName]
        }
    }
    $prop = Get-YMsSqlExtendedProperty -Connection $Connection -Name $Name @propArgs -ErrorAction Ignore
    if ($prop -and $prop.value -eq $Value)
    {
        return
    }

    $sprocArgs = @{
        '@name' = $Name;
        '@value' = $Value;
    }
    foreach ($argPosition in '0', '1', '2')
    {
        foreach ($argKind in @('type', 'name'))
        {
            $argName = "level${argPosition}${argKind}"
            if (-not $PSBoundParameters.ContainsKey($argName))
            {
                continue
            }
            $sprocArgs["@${argName}"] = $PSBoundParameters[$argName]
        }
    }

    $sprocName = 'sp_updateextendedproperty'
    if ($null -eq $prop)
    {
        $sprocName = 'sp_addextendedproperty'
    }

    Invoke-YMsSqlCommand -Connection $Connection `
                         -Text $sprocName `
                         -Parameter $sprocArgs `
                         -Type ([Data.CommandType]::StoredProcedure)
}



function Test-YMsSqlExtendedProperty
{
    <#
    .SYNOPSIS
    Tests for the existence of Microsoft SQL Server extended properties.
 
    .DESCRIPTION
    The `Test-YMsSqlExtendedProperty` function tests if an extended property for an object exists in SQL Server. It
    calls the `fn_listextendedproperty` to get the extended property. Pass the connection to Microsoft SQL Server to the
    connection parameter and the name of the extended property to the `Name` parameter.
 
    To test for the existence of an extended property for an arbitrary object, pass the appropriate level 0, 1, and 2
    types and names for that object to the `Level0Type`, `Level0Name`, `Level1Type`, `Level1Name`, `Level2Type`, and
    `Level2Name` parameters. The value `$null` is allowed for any level type/name parameter. Use
    `[Yodel_MsSql_QueryKeyword]::Default` to pass `default` as the value for any `fn_listextendedproperty` parameter.
 
    To test for the existence of an extended property for a schema, pass the schema's name to the `SchemaName`
    parameter.
 
    To test for the existence of an extended property for a table, pass the table's name and schema name to the
    `TableName` and `SchemaName` parameters, respectively.
 
    To test for the existence of an extended property for a column on a table, pass the column's name, table name, and
    table's schema name to the `ColumnName`, `TableName`, and `SchemaName` parameters, respectively.
 
    .EXAMPLE
    Test-YMsSqlExtendedProperty -Connection $conn -SchemaName 'yodel' -Name 'Yodel_Example'
 
    Demonstrates how to test if a schema extended property exists by passing the schema name to the `SchemaName`
    parameter.
 
    .EXAMPLE
    Test-YMsSqlExtendedProperty -Connection $conn -SchemaName 'yodel' -TableName 'Table_1' -Name 'Yodel_Example'
 
    Demonstrates how to test if a table extended properties exists by passing the table name to the `TableName`
    parameter and the table's schema to the `SchemaName` parameter.
 
    .EXAMPLE
    Test-YMsSqlExtendedProperty -Connection $conn -SchemaName 'yodel' -TableName 'Table_1' -ColumnName 'id' -Name 'Yodel_Example'
 
    Demonstrates how to test if a table column extended property exists by passing the column name to the `ColumnName`
    parameter, the columns's table name to the `TableName` parameter and the table's schema name to the `SchemaName`
    parameter.
    #>

    [CmdletBinding(DefaultParameterSetName='Raw')]
    param(
        # The connection to Microsoft SQL Server.
        [Parameter(Mandatory)]
        [Data.Common.DbConnection] $Connection,

        # The schema name whose extended property to test, or the schema name of the table whose extended property to
        # test.
        [Parameter(Mandatory, ParameterSetName='ForSchema')]
        [Parameter(ParameterSetName='ForTable')]
        [Parameter(ParameterSetName='ForTableColumn')]
        [String] $SchemaName,

        # The table name whose extended property to set, or the table name of the column whose extended property to
        # set.
        [Parameter(Mandatory, ParameterSetName='ForTable')]
        [Parameter(Mandatory, ParameterSetName='ForTableColumn')]
        [String] $TableName,

        # The column name whose extended property to set.
        [Parameter(Mandatory, ParameterSetName='ForTableColumn')]
        [String] $ColumnName,

        # The name of the extended property to test.
        [Parameter(Mandatory)]
        [String] $Name,

        # The value for the `fn_listextendedproperty` function's `level0_object_type` parameter. Default is `NULL`. Use
        # `[Yodel_MsSql_QueryKeyword]::Default` to pass `default` as the value.
        [Parameter(Mandatory, ParameterSetName='RawL0')]
        [Parameter(Mandatory, ParameterSetName='RawL1')]
        [Parameter(Mandatory, ParameterSetName='RawL2')]
        [AllowNull()]
        [Object] $Level0Type,

        # The value for the `fn_listextendedproperty` function's `level0_object_name` parameter. Default is `NULL`. Use
        # `[Yodel_MsSql_QueryKeyword]::Default` to pass `default` as the value.
        [Parameter(Mandatory, ParameterSetName='RawL0')]
        [Parameter(Mandatory, ParameterSetName='RawL1')]
        [Parameter(Mandatory, ParameterSetName='RawL2')]
        [AllowNull()]
        [Object] $Level0Name,

        # The value for the `fn_listextendedproperty` function's `level1_object_type` parameter. Default is `NULL`. Use
        # `[Yodel_MsSql_QueryKeyword]::Default` to pass `default` as the value.
        [Parameter(Mandatory, ParameterSetName='RawL1')]
        [Parameter(Mandatory, ParameterSetName='RawL2')]
        [AllowNull()]
        [Object] $Level1Type,

        # The value for the `fn_listextendedproperty` function's `level1_object_name` parameter. Default is `NULL`. Use
        # `[Yodel_MsSql_QueryKeyword]::Default` to pass `default` as the value.
        [Parameter(Mandatory, ParameterSetName='RawL1')]
        [Parameter(Mandatory, ParameterSetName='RawL2')]
        [AllowNull()]
        [Object] $Level1Name,

        # The value for the `fn_listextendedproperty` function's `level2_object_type` parameter. Default is `NULL`. Use
        # `[Yodel_MsSql_QueryKeyword]::Default` to pass `default` as the value.
        [Parameter(Mandatory, ParameterSetName='RawL2')]
        [AllowNull()]
        [Object] $Level2Type,

        # The value for the `fn_listextendedproperty` function's `level2_object_name` parameter. Default is `NULL`. Use
        # `[Yodel_MsSql_QueryKeyword]::Default` to pass `default` as the value.
        [Parameter(Mandatory, ParameterSetName='RawL2')]
        [AllowNull()]
        [Object] $Level2Name
    )

    Set-StrictMode -Version 'Latest'
    Use-CallerPreference -Cmdlet $PSCmdlet -SessionState $ExecutionContext.SessionState

    $PSBoundParameters['ErrorAction'] = [Management.Automation.ActionPreference]::Ignore
    if (Get-YMsSqlExtendedProperty @PSBoundParameters)
    {
        return $true
    }

    return $false
}



function Test-YMsSqlSchema
{
    <#
    .SYNOPSIS
    Checks if a schema exists in Microsoft SQL Server.
 
    .DESCRIPTION
    The `Test-YMsSqlSchema` function tests if a schema exists in SQL Server. Pass the connection to SQL Server to the
    `Connection` parameter and the schema name to the `Name` parameter. Returns `$true` if the schema exists, and
    `$false otherwise.
 
    .EXAMPLE
    Test-YMsSqlSchema -Connection $conn -Name 'yodel'
 
    Demonstrates how to test if a schema exists by passing the schema name to the `Name` parameter.
    #>

    [CmdletBinding()]
    param(
        # The connection to Microsoft SQL Server.
        [Parameter(Mandatory)]
        [Data.Common.DbConnection] $Connection,

        # The schema name.
        [Parameter(Mandatory)]
        [String] $Name
    )

    Set-StrictMode -Version 'Latest'
    Use-CallerPreference -Cmdlet $PSCmdlet -SessionState $ExecutionContext.SessionState

    $schema = Get-YMsSqlSchema -Connection $Connection -Name $Name -ErrorAction Ignore
    if (-not $schema)
    {
        return $false
    }
    return $true
}


function Test-YMsSqlTable
{
    <#
    .SYNOPSIS
    Checks if a table exists in Microsoft SQL Server.
 
    .DESCRIPTION
    The `Test-YMsSqlTable` function tests if a table exists in SQL Server. Pass the connection to SQL Server to the
    `Connection` parameter and the table name to the `Name` parameter. Returns `$true` if the table exists, and `$false
    otherwise.
 
    .EXAMPLE
    Test-YMsSqlTable -Connection $conn -Name 'yodel'
 
    Demonstrates how to test if a table exists by passing the table name to the `Name` parameter.
    #>
    [CmdletBinding()]
    param(
        [Parameter(Mandatory)]
        [Data.Common.DbConnection] $Connection,

        [String] $SchemaName = 'dbo',

        [Parameter(Mandatory)]
        [String] $Name
    )

    Set-StrictMode -Version 'Latest'
    Use-CallerPreference -Cmdlet $PSCmdlet -SessionState $ExecutionContext.SessionState

    $table = Get-YMsSqlTable -Connection $Connection -SchemaName $SchemaName -Name $Name -ErrorAction Ignore
    if (-not $table)
    {
        return $false
    }
    return $true
}