Finding objects within SSDT database project file.

I’ve been working with database projects recently and for those who have had experience with importing existing databases into SSDT database projects things don’t always go to plan.

One issue that seems to be a common one is objects that don’t build mainly because dependencies have been broken over time and the code is no longer valid and will generate a build error.

I have been fortunate in that I have been able to exclude these from the build without running into a whole bunch of trouble with other objects depending on the ones with errors .

So for these objects I have set the ‘Build Action’ to none, this way I can keep the object and its definition within the project but stop the build errors.

While this has moved me forward I wanted a way to quickly identify these objects in the future without mundanely searching through Visual Studio or the project file xml.

PowerShell to the rescue!

$SolutionPath = "C:\workspace\DatabaseProject"
$SearchType = "None"

$Files = Get-ChildItem -Path $SolutionPath -Filter *.sqlproj -Recurse

$Results = $Files | Foreach-Object {$SqlProj = [xml](Get-Content $_.FullName); $db = $SqlProj.Project.PropertyGroup.RootNameSpace | Select-Object -First 1; $SQlProj.Project.ItemGroup.$SearchType | Foreach-Object {[pscustomobject]@{Database = $db; Object = $_.Include}}}

$Results | Sort-Object Database, Object | Out-GridView