I have a project where each object creation script is stored in a separate file. Between the tables, views, linked servers, stored procedures, functions, jobs and schemas, this results in roughly 400 objects and therefore 400 files. Trying to run these separately for a new installation simply is not feasible; I needed a quick, automated solution to combine all files into a single build script, while at the same time easily control the order of creation so dependent objects can be created in the correct order.

The solution I settled on was to simply store the list of files in the correct order inside a plain text file. I can then use a batch or shell script to compile a single, larger file. Instead of relying on an archaic DOS batch file, I decided to teach myself a bit of Powershell.

It has a considerable amount of push from Microsoft to be bundled starting with their 2008 series of server products (SQL Server 2008, Windows Server 2008 R2). It’s also a free download supported on Windows XP forward. With projects like PASH, you might even say Powershell is a cross-platform solution.

After very little Googling and trial and error, I was able to whip up a small, but effective build script that uses my even-simpler build list. Below is the result of this (save the code to a BuildScript.ps1 file):

1
2
3
4
5
6
7
8
9
10

$BuildList = "BuildList.txt"
$OutputFile = "BuildResult.sql"
 
if (Test-Path $OutputFile) {
	Remove-Item $OutputFile
}
 
foreach ($file in Get-Content $BuildList) {
	Get-Content $file >> $OutputFile
}

This is a very simple script, but let’s pick it apart:

• Lines 1-2 simply declare variables and set their values according to your input and output filenames. $BuildList should point to a file that contains the paths of files separated by line. Each line (that lists a file) can have a subdirectory, or point anywhere really.
• Lines 4-6 checks if your output file exists, and if so, delete it.
• Finally, 8-10 loops through the contents of your build list, storing each iteration in $file. Within the loop, we read the entire contents of the file and append it to your destination build file.

If this is your first time using Powershell, take a quick read of the Microsoft Technet article, Windows PowerShell: Securing the Shell. This will give you a necessary primer on how to get scripts running. Due to intentional security considerations, it’s not as easy as double-clicking a file.