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..

Entice

Pretty sure that you can't do that. The $(ReleaseName) part will work, but I think you need to explicitly call each script by name. Besides, this could be dangerous if even one of those scripts needed to run in a certain order. – Wiesbaden

You could try to write a prebuild action that generated the contents of your post deploytment script? For example it could call on a batch script that iterated over every sql file in a directory and produced a :r statement for them. See #19597392 for an answer mentioning that. This would require some work/investigation on your part though. – Saritasarkaria

Thank you both. The scripts can be run in any order. I'll take a look at the link, although it sounds a bit too complicated.. – Entice

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.

Entice

Where did you put that? – Switchback

Actually in an included profile (in a target-tag), but I guess you could put it in the main one. – Entice

Could you please post how the profiles xml looks like along with your PostScript (the relevant bits) as I can't seem to get this to work. – Hunsinger

How we include is described in my answer here:#27924073 – Entice

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.

Decipher

This is the best solution. Very easy to understand. To make it work, I had to replace %ProjectDir% for %@ProjectDir% in the batch file and add line SET @ProjectDir=%1%; at the beginning of the file. Also, added AllPostScripts.sql to file .gitignore. – Predicative

NOTE: sql files need to be encoded with ANSI, otherwise you'll get syntax error in the combined file because of OEM characters.... I've described the issue here. I've lost an afternoon to it, so I hope this comment save you the hassle. – Predicative

Also, using command type instead copy is better because it will automatically add a carriahe return between script. This give better readablity in the result script and also give the piece of mind about not having the last word of a script be merged with the first world of next script. – Predicative

Answer 3

Building upon @SAS answer, here is what I did to get this working using MSBuild. Basically, the idea is that we add a pre-build target that auto-generates a post-deployment script referencing all the scripts in the source folder.

In the .sqlproj add the following at the end of the file:

<Target Name="BeforeBuild">
  <PropertyGroup>
    <MyAutogeneratedScriptPath>$(ProjectDir)Scripts\Post-deployment\MyScript.autogenerated.sql</MyAutogeneratedScriptPath>
  </PropertyGroup>
  <ItemGroup>
    <MyScriptsLocation Include="$(ProjectDir)Scripts\Post-deployment\RunAll_1\*.sql" />
    <MyScriptsLocation Include="$(ProjectDir)Scripts\Post-deployment\RunAll_2\*.sql" />
  </ItemGroup>
  <WriteLinesToFile File="$(MyAutogeneratedScriptPath)" Lines="-- This is an auto-generated file, any changes made will be overwritten" Overwrite="true" />
  <WriteLinesToFile File="$(MyAutogeneratedScriptPath)" Lines="@(MyScriptsLocation->':r %22%(FullPath)%22', '%0D%0A')" Overwrite="false" />
</Target>

And then in your main post-deployment script file, include the MyScript.autogenerated.sql file.

Also, you might also want to add *.autogenerated.sql to your .gitignore file.

Neustria