SQLCMD include all scripts in folder

Question

In my Post Deployment Script, I would like to include all script files in a folder using a wildcard like this: :r .\$(ReleaseName)\*.sql

Is there a way to do this? I can't find any..

Answer 1

I got it working:

<MyFilesPath Include="$(ProjectDir)MyPath\*.sql"/>
<MyFiles Include="@(MyFilesPath->':r %22..\Scripts\%(filename)%(extension)%22%0D%0A', '')"/>

Then I include @MyFiles in my PostScript-file.

Answer 2

I took a different approach that was easier for me to understand.

I simply added code to the Pre-build event in the database project properties page that copies the script files into a single file. I call a bat file and pass in the project path as a parameter because it's much nicer to edit the file than trying to edit in that little textbox in the properties page.

$(ProjectDir)PreBuildEvent.bat "$(ProjectDir)"

I set the contents of the bat to this:

copy %ProjectDir%DbUpdateScripts\*-Pre.sql %ProjectDir%DbUpdateScripts\AllPreScripts.sql
copy %ProjectDir%DbUpdateScripts\*-Post.sql %ProjectDir%DbUpdateScripts\AllPostScripts.sql

Then just include those files in your actual pre and post deploy scripts.

:r .\DbUpdateScripts\AllPreScripts.sql
:r .\DbUpdateScripts\AllPostScripts.sql

And finally, add AllPreScripts.sql and AllPostScripts.sql to your .gitignore file if you have one to prevent them from getting added to source control.