Combining multiple SQL script files into one with PowerShell

Situation in a client project today: I had a folder with about 40 different small SQL Server script files and needed a way to run them against a test database. Surely, I could load each of them into SQL Server Management Studio (SSMS) and execute them one by one.

That would have been quite tedious, and a little error-prone too, so I decided to write a simple PowerShell script that reads those files and outputs a Combined.sql file with all the SQL scripts combined into one big file. This file is then easy to execute in one go.

Here's the script I came up with:

$output = ".\Combined.sql"
Get-ChildItem -Path . -Filter *.sql |
    Where-Object { $_.FullName -ne (Resolve-Path $output -ErrorAction SilentlyContinue).Path } |
    Sort-Object Name |
    ForEach-Object {
        "-- ================================================"
        "-- Source file: $($_.Name)"
        "-- ================================================"
        ""
        Get-Content $_.FullName
        ""
        "GO"
        ""
    } | Set-Content -Path $output -Encoding UTF8

Notice how I have excluded the Combined.sql file itself from the operation (in case you run the script twice). Also, I chose to add the GO statement at the end of each file, which is good for SSMS usage. If you don't like it, just strip it away.

Happy database'ing!

PS. This same script works in other cases too, where you want to combine multiple text files into one. Maybe not programming code, but SQL, JSONL and CSV would work, for example.