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. & { Join-Path -Path $script:moduleRoot -ChildPath 'Functions' Join-Path -Path $script:moduleRoot -ChildPath 'Functions\MsSql' } | Where-Object { Test-Path -Path $_ } | Get-ChildItem -Filter '*.ps1' | ForEach-Object { . $_.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 Get-YMsSqlTableColumn { <# .SYNOPSIS Gets metadata about a table's columns from Microsoft SQL Server. .DESCRIPTION The `Get-YMsSqlTableColumn` function gets metadata about a table's columns 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-YMsSqlTableColumn -Connection $conn -Name 'Table_1' Demonstrates how to get metadata for a table's column in the `dbo` schema. In this example, returns the `[dbo].[Table_1]` table's record from `sys.tables`. .EXAMPLE Get-YMsSqlTableColumn -Connection $conn -SchemaName 'yodel' -Name 'Table_1' Demonstrates how to get metadata for a table's columns 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] $TableName, [String] $Name ) Set-StrictMode -Version 'Latest' Use-CallerPreference -Cmdlet $PSCmdlet -SessionState $ExecutionContext.SessionState $queryArgs = @{ '@tableName' = $TableName; '@schemaName' = $SchemaName; } $nameClause = '' if ($Name) { $nameClause = " and c.name=@name" $queryArgs['@name'] = $Name } $query = @" select c.* from sys.columns c join sys.tables t on c.object_id=t.object_id join sys.schemas s on t.schema_id=s.schema_id where t.name=@tableName and s.name=@schemaName${nameClause} "@ $result = Invoke-YMsSqlCommand -Connection $Connection -Text $query -Parameter $queryArgs if (-not $result) { $msg = "Table [${SchemaName}].[${TableName}] does not exist." if ($Name) { $msg = "Column [${Name}] does not exist on table [${SchemaName}].[${TableName}]." } 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. Returns `$true` if the exteneded property exists. Otherwise, returns `$false`. Use the `PassThru` switch to return the property metadata instead of `$true`. .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, # Return extended property metadata instead of `$true` if the property exists. [switch] $PassThru ) Set-StrictMode -Version 'Latest' Use-CallerPreference -Cmdlet $PSCmdlet -SessionState $ExecutionContext.SessionState $getArgs = $PSBoundParameters [void]$getArgs.Remove('PassThru') $property = Get-YMsSqlExtendedProperty @getArgs -ErrorAction Ignore if (-not $property) { return $false } if ($PassThru) { return $property } return $true } 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. Use the `PassThru` switch to return the schema metadata instead of `$true`. .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, # Return schema metadata instead of `$true` if the schema exists. [switch] $PassThru ) 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 } if ($PassThru) { return $schema } 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. Use the `PassThru` switch to return the table metadata instead of `$true`. .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, # Return the table metadata instead of `$true` if the table exists. [switch] $PassThru ) 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 } if ($PassThru) { return $table } return $true } function Test-YMsSqlTableColumn { <# .SYNOPSIS Checks if a column exists on a table in Microsoft SQL Server. .DESCRIPTION The `Test-YMsSqlTableColumn` function tests if a column exists on a table in SQL Server. Pass the connection to SQL Server to the `Connection` parameter, the table name to the `TabkeName` parameter, and the column name to the `ColumnName` parameter. Returns `$true` if the column exists, and `$false` otherwise. If the table is in a custom schema, pass the schema name to the `SchemaName` parameter. .EXAMPLE Test-YMsSqlTableColumn -Connection $conn -TableName 'yodel' -Name 'id' Demonstrates how to test if a column exists on a table by passing the table name to the `TableName` parameter and the column name to the `Name` parameter. #> [CmdletBinding()] param( [Parameter(Mandatory)] [Data.Common.DbConnection] $Connection, [String] $SchemaName = 'dbo', [Parameter(Mandatory)] [String] $TableName, [Parameter(Mandatory)] [String] $Name, # If the column exists, return it instead of `$true`. [switch] $PassThru ) Set-StrictMode -Version 'Latest' Use-CallerPreference -Cmdlet $PSCmdlet -SessionState $ExecutionContext.SessionState $column = Get-YMsSqlTableColumn -Connection $Connection ` -SchemaName $SchemaName ` -TableName $TableName ` -Name $Name ` -ErrorAction Ignore if (-not $column) { return $false } if ($PassThru) { return $column } return $true } |