Powershell: find SQL files orphaned from SSMS projects

We’re using SQL Management Studio to manage our database scripts.  (Yes, VS “Data Dude” is under evaluation, but it has lots of quirks we haven’t had time to get a grasp on yet.)  Since making DB development a collaborative, source-controlled process is new itself, we needed some extra checks & balances.  I remembered that James had a script for reconciling project files with what’s actually on disk.  Although SSMS doesn’t use msbuild for its project structure, it was still fairly easy to adapt.

function find-orphan {
  param([string]$proj = $(throw 'ssmssqlproj file is required'))
  
  $proj = resolve-path $proj
  $dir = split-path $proj
  
  $xml = [xml](cat $proj)
  $files_from_proj = ($xml.SqlWorkbenchSqlProject.Items.LogicalFolder | 
    where { $_.name -eq "Queries" }).items.filenode |
    foreach { join-path $dir $_.name } | 
    sort
    
  $files_from_dir = ls $dir -r -filter *.sql |
    foreach { $_.fullname } |
    sort
    
  compare $files_from_proj $files_from_dir
}

Leave a Reply