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
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: