SQL: Use PowerShell to Generate a Database Object From Another Object

Have you ever wondered about the prospect of automating T-SQL executions on Windows? As your dinosaur certified DBA, I give you a couple of functions to generate a View Object using an existing Table Object. Enjoy.

# Set these variables
$sqlServer=$env:computername
$saCred=$null
$databaseName='Test_Database'
$objectName='[dbo].[TestView]'
$objectType='view'
$fromObject='[dbo].[Some_Table]'

# call function
$tsql=tsqlCommandToCreateObject -sqlServer $destinationSqlServer `
            -databaseName $databaseName `
            -objectName $objectName `
            -objectType $objectType `
            -fromObject $fromObject `
            -saCred $destinationSaCred
invokeTsql -sqlServer $destinationSqlServer `
            -databaseName $databaseName `
            -tSql $tsql `
            -saCred $destinationSaCred
function checkDatabaseObject{
    param(
        [Parameter(Mandatory=$true)][String[]]$sqlServer=$env:computername,
        [Parameter(Mandatory=$true)][String[]]$databaseName,
        [Parameter(Mandatory=$true)][String[]]$objectName,
        [Parameter(Mandatory=$false)][String[]]$objectType,
        [Parameter(Mandatory=$false)]$saCred
        )
    $ErrorActionPreference='stop'  

    function includeSqlPs{
        if(!(get-command invoke-sqlcmd)){
            if(!('NuGet' -in (get-packageprovider).Name)){    
                try{
                    #Preempt this error: Unable to resolve package source 'https://www.powershellgallery.com/api/v2'
                    [Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
                    Install-PackageProvider -Name NuGet -MinimumVersion 2.8.5.201 -Force -ErrorAction SilentlyContinue;
                    #Resolve this error: PackageManagement\Install-Package : No match was found for the specified search criteria and module name 'sqlps'. Try Get-PSRepository to see all available registered module repositories.
                    #Register-PSRepository -Default
                    #Register-PSRepository -Name PSGallery -InstallationPolicy Trusted -Verbose
                    }
                catch{
                    write-warning $error[0].Exception.Message
                    }            
                }
            Install-Module -Name 'sqlserver' -Force -Confirm:$false
            try{
                Update-SessionEnvironment -ea stop
                }
            catch{
                # Prempt these errors: Install Choco to peruse its prepackaged libraries
                # Update-SessionEnvironment : The term 'Update-SessionEnvironment' is not recognized as the name of a cmdlet
                # The term 'refreshenv' is not recognized as the name of a cmdlet, function, script file, or operable program
                # Install Chocolatey
                if (!(Get-Command choco.exe -ErrorAction SilentlyContinue)) {
                [Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
                Set-ExecutionPolicy Bypass -Scope Process -Force; iex ((New-Object System.Net.WebClient).DownloadString('https://chocolatey.org/install.ps1'))}

                # Defining $ENV:ChocotaleyInstall so that it would be called by refreshenv
                            $ENV:ChocolateyInstall = Convert-Path "$((Get-Command choco).Path)\..\.."   
                            Import-Module "$env:ChocolateyInstall\helpers\chocolateyProfile.psm1"
                            Update-SessionEnvironment
                }            
            }
            import-module 'SQLPS'
        }  

    try{
        $objectTypeAbbrevations=@{
            'view'='V'
            'procedure'='P'
            'table'='U'
            }
        $sqlCheckCommand =  if(!$objectType){"
                                USE [$databaseName]
                                GO
                                SELECT CASE
                                    WHEN (OBJECT_ID('$objectName')) IS NULL
                                    THEN CAST(0 AS BIT) ELSE CAST(1 AS BIT)
                                END
                                "
                            }else{"
                                USE [$databaseName]
                                GO
                                SELECT CASE
                                    WHEN (OBJECT_ID('$objectName','$($objectTypeAbbrevations[$objectType])')) IS NULL
                                    THEN CAST(0 AS BIT) ELSE CAST(1 AS BIT)
                                END
                                "
                                }        
      
        $objectExists=invoke-command -Credential $saCred -ComputerName $sqlServer -ScriptBlock{
                            param($includeSqlPs,$sqlCheckCommand)
                            [ScriptBlock]::Create($includeSqlPs).invoke()                          
                            return (invoke-sqlcmd $sqlCheckCommand -ServerInstance $env:computername).Column1
                            } -Args ${function:includeSqlPs},$sqlCheckCommand
                                              
        return $objectExists
    }catch{
        # $ErrorActionPreference = "Stop" in conjunction with Write-Error = terminating error
        write-error $error[0].Exception.Message
        return $false
        }
}

function tsqlCommandToCreateObject{
    param(
        [Parameter(Mandatory=$true)][String[]]$sqlServer=$env:computername,
        [Parameter(Mandatory=$true)][String[]]$databaseName,
        [Parameter(Mandatory=$true)][String[]]$objectName,
        [Parameter(Mandatory=$true)][String[]]$objectType,
        [Parameter(Mandatory=$true)][String[]]$fromObject,
        [Parameter(Mandatory=$true)]$saCred
        )
    # Validate input
    $validObjectTypes='view','procedure','table'
    $validatedObjectType=!(!($validObjectTypes|?{$_ -eq $objectType}))
    if(!$validatedObjectType){
        write-warning "Object type $objectType is invalid."
        return $null
        }
    $objectTypeAbbrevations=@{
        'view'='V'
        'procedure'='P'
        'table'='U'
        }
    $prepTSql="
        USE [$databaseName]
        GO
        DECLARE @sqlCmd nvarchar (1000000000)
        BEGIN
            IF (OBJECT_ID('$objectName', '$($objectTypeAbbrevations[$objectType])')) IS NULL          
            BEGIN 
                SELECT @sqlCmd = 'CREATE $objectType $objectName as SELECT * FROM $fromObject'
                EXEC sp_executesql @sqlCmd
            END
        END
  
        "
    $tSql="
        USE [$databaseName]
        GO
        DECLARE @sqlCmd nvarchar ($($prepTSql.Length +100))
        BEGIN
            IF (OBJECT_ID('$objectName', '$($objectTypeAbbrevations[$objectType])')) IS NULL          
            BEGIN 
                SELECT @sqlCmd = 'CREATE $objectType $objectName as SELECT * FROM $fromObject'
                EXEC sp_executesql @sqlCmd
            END
        END
  
        "
    return $tSql
}

function invokeTsql{
    param(
        [Parameter(Mandatory=$true)][String[]]$sqlServer=$env:computername,
        [Parameter(Mandatory=$true)][String[]]$databaseName,
        [Parameter(Mandatory=$true)][String[]]$tSql,
        [Parameter(Mandatory=$false)]$saCred
        )
    $ErrorActionPreference='stop'

    function includeSqlPs{
        if(!(get-command invoke-sqlcmd)){
            if(!('NuGet' -in (get-packageprovider).Name)){    
                try{
                    #Preempt this error: Unable to resolve package source 'https://www.powershellgallery.com/api/v2'
                    [Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
                    Install-PackageProvider -Name NuGet -MinimumVersion 2.8.5.201 -Force -ErrorAction SilentlyContinue;
                    #Resolve this error: PackageManagement\Install-Package : No match was found for the specified search criteria and module name 'sqlps'. Try Get-PSRepository to see all available registered module repositories.
                    #Register-PSRepository -Default
                    #Register-PSRepository -Name PSGallery -InstallationPolicy Trusted -Verbose
                    }
                catch{
                    write-warning $error[0].Exception.Message
                    }            
                }
            Install-Module -Name 'sqlserver' -Force -Confirm:$false
            try{
                Update-SessionEnvironment -ea stop
                }
            catch{
                # Prempt these errors: Install Choco to peruse its prepackaged libraries
                # Update-SessionEnvironment : The term 'Update-SessionEnvironment' is not recognized as the name of a cmdlet
                # The term 'refreshenv' is not recognized as the name of a cmdlet, function, script file, or operable program
                # Install Chocolatey
                if (!(Get-Command choco.exe -ErrorAction SilentlyContinue)) {
                [Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
                Set-ExecutionPolicy Bypass -Scope Process -Force; iex ((New-Object System.Net.WebClient).DownloadString('https://chocolatey.org/install.ps1'))}

                # Defining $ENV:ChocotaleyInstall so that it would be called by refreshenv
                            $ENV:ChocolateyInstall = Convert-Path "$((Get-Command choco).Path)\..\.."   
                            Import-Module "$env:ChocolateyInstall\helpers\chocolateyProfile.psm1"
                            Update-SessionEnvironment
                }            
            }
            import-module 'SQLPS'
        }

    try{
        if ($saCred){
            $session=new-pssession $sqlServer -Credential $saCred
        }else{
            $session=new-pssession $sqlServer
            }
        if(!$session){
            write-warning "Unable to create a WinRM session toward $sqlServer"
            return $false
            }

        $sqlExecResult=invoke-command -Session $session {
            param($includeSqlPs,$databaseName,$tSql)        
            $ErrorActionPreference='stop'

            function confirmation($content,$testValue="I confirm",$maxAttempts=3){
                    $confirmed=$false;
                    $attempts=0;        
                    $content|write-host
                    write-host "Please review this content for accuracy.`r`n"
                    while ($attempts -le $maxAttempts){
                        if($attempts++ -ge $maxAttempts){
                            write-host "A maximum number of attempts have reached. No confirmations received!`r`n"
                            break;
                            }
                        $userInput = Read-Host -Prompt "Please type in this value => $testValue <= to confirm. Input CANCEL to skip this item";
                        if ($userInput.ToLower() -eq $testValue.ToLower()){
                            $confirmed=$true;
                            write-host "Confirmed!`r`n";
                            break;                
                        }elseif($userInput -like 'cancel'){
                            write-host 'Cancel command received.'
                            $confirmed=$false
                            break
                        }else{
                            cls;
                            $content|write-host
                            write-host "Attempt number $attempts of $maxAttempts`: $userInput does not match $testValue. Try again or Input CANCEL to skip this item`r`n"
                            }
                        }
                    return $confirmed;
                }

            # Include SQL PowerShell tools
            [ScriptBlock]::Create($includeSqlPs).invoke()              

            $dbExists=(invoke-sqlcmd "SELECT CASE WHEN DB_ID('$databaseName') IS NULL THEN CAST(0 AS BIT) ELSE CAST(1 AS BIT) END").Column1         
            if($dbExists){
                $confirmed=confirmation "Please confirm this T-SQL statement. Ctrl+C to Cancel`r`n$tSql"
                if($confirmed){        
                    try{
                        invoke-sqlcmd -query $tSql
                        write-host "T-SQL has been committed successfully." -ForegroundColor Green
                        return $true
                    }catch{
                        write-host $error[0].Exception.Message -ForegroundColor Red
                        return $false
                        }
                }else{
                    write-host "T-SQL has been cancelled by $(whoami)" -ForegroundColor Yellow
                    return $false
                    }
            }else{
                write-warning "Database $databaseName does not match any valid DB on $env:computername"
                return $false
                }
            } -Args ${function:includeSqlPs},$databaseName,$tsql
        Remove-PSSession $session
        return $sqlExecResult
    }catch{
        Write-Warning $error[0].Exception.Message
        return $false
        }
}

Leave a Reply

Your email address will not be published. Required fields are marked *