Auto Add Sql Files to Database Project
When working on large SQL Database Projects in Visual Studio files and folders can get accidently dropped or duplicated during git merges etc. Here is a way to automatically add all sql files in the project folder to the Sql Database Project so that the repository is kept clean. Within your .sqlproj file add a label of “Folders” to the <ItemGroup> containing all the <Folder ... /> entries and a label of “SqlFiles” to the <ItemGroup> containing all the <Build ... /> entries like so: ... <ItemGroup Label="Folders"> <Folder Include="###" /> </ItemGroup> <ItemGroup Label="SqlFiles"> <Build Include="###.sql" /> </ItemGroup> Copy this script into a file called AutoAddSql.ps1: $Filename = $args[0] # Make .NET's current directory follow PowerShell's current directory, if possible. if ($PWD.Provider.Name -eq 'FileSystem') { [System.IO.Directory]::SetCurrentDirectory($PWD) } # Open SqlProj file as XmlDocument $SqlProj = New-Object -TypeName System.Xml.XmlDocument $Namespace = New-Object -TypeName System.Xml.XmlNamespaceManager -ArgumentList $SqlProj.NameTable $SqlProj.Load($Filename) $Namespace.AddNamespace("ns", $SqlProj.Project.xmlns) # Fetch Folders and SqlFiles ItemGroups $FoldersXml = $SqlProj.SelectSingleNode("//ns:ItemGroup[@Label='Folders']", $Namespace) $SqlFilesXml = $SqlProj.SelectSingleNode("//ns:ItemGroup[@Label='SqlFiles']", $Namespace) # Drop all ChildNodes of the ItemGroups @($FoldersXml.ChildNodes) | % { $FoldersXml.RemoveChild($_) } > $null @($SqlFilesXml.ChildNodes) | % { $SqlFilesXml.RemoveChild($_) } > $null # Get Folders and Files $Dirs = @(Get-ChildItem -Exclude @(".git",".vs","bin","obj") -Directory | %{ $_ | Resolve-Path -Relative }) $Dirs += $Dirs | %{ $_ | Get-ChildItem -Recurse | Where-Object -Property PSIsContainer -Eq true | Resolve-Path -Relative } $Dirs = $Dirs | Sort-Object -Unique $Folders = $Dirs -replace "^\.\\","" $Files = $Dirs | Get-ChildItem -Recurse -Filter *.sql | % { $($_.FullName | Resolve-Path -Relative) -replace "^\.\\","" } | Sort-Object -Unique # Update SqlProj $Folders | % { $Element = $SqlProj.CreateElement("Folder", $SqlProj.Project.xmlns) $Element.SetAttribute("Include", $_) $FoldersXml.AppendChild( $Element ) } > $null $Files | % { $Element = $SqlProj.CreateElement("Build", $SqlProj.Project.xmlns) $Element.SetAttribute("Include", $_) $SqlFilesXml.AppendChild( $Element ) } > $null # Output $SqlProj.Save($Filename) You can then run the script from the working directory containing you .sqlproj project file like so: ./AutoAddSql.ps1 "database_name.sqlproj" This will ensure that all .sql scripts and folder paths are represented within your SQL Database Project. You could even add this as a PreBuild event to ensure it’s always up-to-date.