Detects SQL files that are missing from sqlproj files within the given path

DetectMissingSQLFiles.ps1

###########################################################################
# Detects SQL files that are missing from sqlproj files within the given
# path. Useful for catching mis-merges that could cause a SProc to not
# get deployed, or a table index to get missing.
# 
# Author: Matthew Kelly (@Badgerati)
# Date: July 27 2015
# 
# path:  Path to where your .sqlproj files reside
#
# Example:
# > .\DetectMissingSQLFiles.ps1 -path path\to\sqlproj\folder
###########################################################################
param (
	[Parameter(Mandatory=$true)]
	[string] $path
)

# Returns the files with the given extension within the given path, recursively
function Get-Files($path, $extension) {
    return gci -re -in $extension $path
}

# Returns all files included within the project files that match the passed regex
function Get-Included-Files($path, $projExtension, $regex) {
    $proj = Get-Files $path $projExtension

    foreach ($file in $proj)
    {
        $dir = Split-Path $file.FullName

        foreach ($line in (gc $file))
        {
            if ($line -match $regex)
            {
                Join-Path $dir $matches[2]
            }
        }
    }
}

# Returns all SQL files included within all sqlproj files within the given path
function Get-Included-SQL-Files($path) {
    return Get-Included-Files $path *.sqlproj '(Build|None|PostDeploy|PreDeploy)\s+Include="(.*?.sql)"'
}

# Returns all SQL files that are in the given path, but are not in a sqlproj file
function Get-Missing-SQL-Files($path) {
    $map = @{}
    Get-Included-SQL-Files $path | %{ if (!$map.Contains($_)) { $map.Add($_, $true) } }
    return Get-Files $path *.sql | ?{ -not $map.Contains($_.FullName) }
}

if (!(Test-Path $path)) {
    Write-Host "Path does not exist"
    return
}

Write-Host "Detecting Missing SQL Files in: $path"
$missing = Get-Missing-SQL-Files $path
    
if ($missing.Count -ne 0) {
    $files = ""
    foreach ($file in $missing)
    {
        $files += "`n`t> $file"
    }

    Write-Host "The following SQL files are missing from their respective sqlproj files:$files"
}
else {
    Write-Host "All SQL files are included in the sqlproj files"
}