Introduction

This article contains a collection of useful PowerShell scripts for T-SQL developers.

Figure 1: Ball valves and PVC pipes

Combine T-SQL script files

This script reads all files in a folder ending with .SQL and adds their content to one single file. This comes in handy, if you need to email the script text for example.

$Output = "C:\Users\KWG\Desktop\QualityLimitQueries.sql"

$Files = Get-ChildItem -Path "C:\Users\KWG\Desktop\New Quality Limit Tables" -Filter "*.sql"

Set-Content -Path $Output -Value "-- Klaus Graefensteiner $(Get-Date)"
foreach($File in $Files)
{
    Add-Content -Path $Output -Value "-- ---------------------------------------"
    Add-Content -Path $Output -Value "-- $($File.Name)"
    Add-Content -Path $Output -Value "-- ---------------------------------------"
    
    $Lines = Get-Content -Path $File.FullName
    foreach($Line in $Lines)
    {
        Add-Content -Path $Output -Value $Line
    }
}

Create Grant Execute script for create stored procedure files

This script looks for files ending in .SQL and starting with Create_sp. The file names contain the names of the stored procedures that they are going to create. The script takes the names and creates a new script for granting execute permissions to each of the stored procedure to a user called ReportAdmin.

$SQLScriptsFilePath = "C:\Documents and Settings\KlausG\My Documents\Reporting\SQL"
$InstallCommandsFilePath = Join-Path -Path $SQLScriptsFilePath -ChildPath "SQLGrantExecutes.txt"
$UserName = "ReportAdmin"


Set-Content -Path $InstallCommandsFilePath -Value "-- GRANT PERMISSIONS"
$SQLFiles = Get-ChildItem -Path $SQLScriptsFilePath  | Where-Object {$_.Name -match "^Create_sp.*?$"}

foreach($File in $SQLFiles)
{
    
    $LineTemplate = "GRANT EXECUTE ON {0} TO {1} GO" -f $File.Name, $UserName
    
    $Line = $LineTemplate
    $Line = $Line.Replace("Create_", "").Replace(".sql", "")
    Add-Content -Path $InstallCommandsFilePath -Value $Line

}

Create a SQLCMD batch file to install stored procedures

This script gets all the create stored procedure scripts in a folder and generates a batch file for SQLCMD.

$SQLScriptsFilePath = "C:\Documents and Settings\KlausG\My Documents\Reporting\SQL"
$InstallCommandsFilePath = Join-Path -Path $SQLScriptsFilePath -ChildPath "SQLSetupCommandLines.txt"


Set-Content -Path $InstallCommandsFilePath -Value "REM Commands"
$SQLFiles = Get-ChildItem -Path $SQLScriptsFilePath  | Where-Object {$_.Name -match "(^Create.*?$)|(^Insert.*?$)"}

foreach($File in $SQLFiles)
{
    
    $LineTemplate = "SQLCMD -S %1 -d %2 -E -i `"{0}`"" -f $File.Name
    $Line = $LineTemplate
    Add-Content -Path $InstallCommandsFilePath -Value $Line

}

Ausblick

As always PowerShell is a timesaver.