The other day we talked about some of the benefits of using SQL Server Management Studio (SSMS) projects. I did show one downside of how the .SQL files inside the project could become out of alphabetical order and there was no way to easily get them back into order.
Well here is a quick script to put them back into order:
#----------------------------------------------------------------------------- #Script Info #----------------------------------------------------------------------------- #SFIBICH #4/10/12 #Version 2.0 #order-SQLproj.ps1 #This script saves a backup file as a .bak #This script will search the USERPROFILE enviorment variable + My documents #as its starting place for .ssmssqlproj files #This script allows args that will replace the default search path #Use at your own risk, no guarantees #----------------------------------------------------------------------------- #----------------------------------------------------------------------------- #Variables #----------------------------------------------------------------------------- #$ars[0] - relative path to the file name #$scriptCall = path to this file when executed #$iCounter = counter variable #$pCounter = counter for progress bar #$xmlObjectmlObject = xml object varaible #$files = list of all dtproj files #$Items = list of dtspackages node items inside the xml document #----------------------------------------------------------------------------- $scriptCall=$^; $pCounter=0; #----------------------------------------------------------------------------- #Figure out if the script should execute or give help(only kind of works) #----------------------------------------------------------------------------- if ($args[0].length -gt 0) { $fileLocation=$args[0] if ($fileLocation -eq '?') { get-content $scriptCall | ? {$_.length -gt 0} | ? {$_.substring(0,1) -eq '#' } exit } else{ date;$files=gci -path ($fileLocation) -include *.dtproj -recurse;date } }else{ write-warning('starting search for *.ssmssqlproj files at '+$env:UserProfile+'\My Documents\ this is a recursive search and may take a while') date;$files=gci -path ($env:UserProfile+'\My Documents\') -include *.ssmssqlproj -recurse;date } #----------------------------------------------------------------------------- #Loop through all of the *.ssmssqlProj files #----------------------------------------------------------------------------- $files | % { $pCounter+=1 write-progress "re-ordering ssmssqlproj files- progress" "% complete" -perc ($pCounter/$files.length*100) $_.Name [xml]$xmlObject = get-content $_.FullName #----------------------------------------------------------------------------- #Backup each XML object (ssmssqlproj file) then read the items. #----------------------------------------------------------------------------- $xmlObject.save($_.FullName+'.bak') $Items=$xmlObject.SqlWorkbenchSQLProject.Items.LogicalFolder[1].Items.FileNode | sort-object -property name $iCounter=0 #----------------------------------------------------------------------------- #Remove items then add them back to the XML object #----------------------------------------------------------------------------- $Items| % {if ($iCounter -eq 0) {$iCounter+=1} else {$xmlObject.SqlWorkbenchSQLProject.Items.LogicalFolder[1].Items.RemoveChild($_)}} | out-null $Items| % {if ($iCounter -eq 1) {$iCounter+=1} else {$xmlObject.SqlWorkbenchSQLProject.Items.LogicalFolder[1].Items.AppendChild($_)}} | out-null #----------------------------------------------------------------------------- #Save each XML object (ssmssqlproj file) #----------------------------------------------------------------------------- $xmlObject.save($_.FullName) } |
If you run this script while you have the project open in SSMS you may get a warning but just choose Reload and continue.
You will be rewarded with a reordered ssmsSQLProj file.
Thats it short and sweet. Hopefully the PowerShell script is straight forward it doesn’t need an explanation. I will note that the help functionality of the script doesn’t work exactly right it requires you to run the script once and then the second time to call it with the ? parameter. You could call it both times with the ? if you want. I’m working on this piece and it will be updated when I fix it.