SQL: Using PowerShell to Check if a Table, View, or Stored Procedure Exists

#Usage 

# set variables
$sqlServer='sql-server04'
$databaseName="Test_Database"
$objectType='view'
$objectName='[dbo].[Test_View]'
$saCred=get-credential

# Call function
checkDatabaseObject $sqlServer $databaseName $objectName $objectType $saCred
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
        }
}

Leave a Reply

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