Sql Server Powershell

Append SQL Files into one File with PowerShell

,

Here’s a quick script to append all *.sql files into one file.  This automagically appends a ‘GO’ statement between the files as a just in case.  One issue (that I’m currently working on) is that this currently scripts the files by the name of the file.  If the files have to be run in a certain order and has the order number in the file name (eg, 1_CreateTables.sql, 2_AddData.sql, etc…) it will mess the order up if there are more than 9 files.  For example, if there were over 10 files, the order would be 1_CreateFiles.sql, 10_AddProcedures.sql.  I’m still working on correcting this.

This will create a file called out.sql in the originating directory that contains all the contents of the files.

param(
	#[string]$path = $(throw "You must enter the path to the .sql files you want to append."),
	[string]$NewFileName = "Out.sql"
)

$path = "\\rbnnorfasp01p2\RCDDev1\Pipeline\Database\20111015\SQL Scripts\MSA Scripts\02_MSA_SProcScripts"
$outFile = "$path\$NewFileName"

cls
if((Test-Path $outFile) -eq $true) {Remove-Item -Path $outFile -Force}

$files = Get-ChildItem -LiteralPath $path -Include "*.sql" -Recurse | Sort-Object -Property Name

New-Item -ItemType file -Path $outFile -Force | Out-Null

foreach($file in $files)
{
	Write-Host "Appending file $file..." -ForegroundColor Gray
	$content = Get-Content -Path $file.FullName
	Add-Content -Path $outFile "----------------------------------------------------------------------------------------------------------------------------------------------------------------"
	Add-Content -Path $outFile "--		$File"
	Add-Content -Path $outFile "----------------------------------------------------------------------------------------------------------------------------------------------------------------"
	Add-Content -Path $outFile $content
	Add-Content -Path $outFile "GO`n"
}

Write-Host "Completed file $outFile" -ForegroundColor DarkGreen

Responses

  1. Sush Avatar
    Sush

    This script is appending all the files in my directory to the Outfile is there any reason why it is pulling other files in the directory and not just .sql files

    Reply
    1. Scott Newman Avatar
      Scott Newman

      Try this instead (I rarely use -Include and usually always do it this way now…)
      $files = Get-ChildItem -Path $path -Recurse -File | ?{$_.Extension -eq ‘.sql’} | Sort-Object -Property Name

      Reply
      1. Sush Avatar
        Sush

        Thanks that worked!

  2. sush Avatar
    sush

    Is there a way to iterate this to create an Outfile by combining .sql in each folder and skip a folder if it is empty

    Reply
  3. sush Avatar
    sush

    I tried to do this foreach ($folder in Get-ChildItem $sourcefolder)
    {
    $outfile = “source\$folder\$NewFileName”
    added your full script in
    }
    What this did was it created an outfile in folder1 with both folder1 and folder 2 files appended when what i wanted was only the files in folder1 to be appended to outfile and in folder 2 it created the outfile with duplicate copy of what is in folder1(folder1+folder2 files+ folder1+folder 2 files) when what i want is only folder 2 files appended to outfile in this folder

    Reply
  4. Richard Avatar
    Richard

    If you want to sort the file names by the leading number, change from

    | Sort-Object -Property Name

    to:

    | Sort -Property @{Expression = { [int]($_.BaseName -split ‘-‘)[0] }}

    What this does is calculates the name by splitting on the delimiter (‘-‘) then takes the left potion ([0]), casts it to an integer (so that 10 doesn’t get groupped with 1), then sorts it.

    Reply

Leave a reply to sush Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.