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
}
}
