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

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. — Peter Schott, Mar 13 '14 at 16:33
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 http://stackoverflow.com/questions/19596892/custom-build-action-for-sqlproj/19616604#19616604 for an answer mentioning that. This would require some work/investigation on your part though. — Kevin Cunnane, Mar 13 '14 at 23:26
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.. — SAS, Mar 14 '14 at 07:59

score 4 · Accepted Answer · answered Mar 31 '15 at 06:35

4

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.

answered Mar 31 '15 at 06:35

SAS

3,943
2
27
48

Where did you put that? – Apocatastasis Jun 10 '15 at 18:18
Actually in an included profile (in a target-tag), but I guess you could put it in the main one. – SAS Jun 11 '15 at 03:29
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. – mengstrom Aug 20 '15 at 11:30
How we include is described in my answer here:http://stackoverflow.com/questions/27923573/how-to-apply-transforms-to-ssdt-publish-profiles/27924631#27924631 – SAS Aug 27 '15 at 08:49

score 1 · Answer 2 · answered Jun 06 '17 at 22:45

1

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.

answered Jun 06 '17 at 22:45

adam0101

29,096
21
96
174

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. – AXMIM Apr 05 '19 at 16:04
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](https://stackoverflow.com/questions/55542197/combine-sql-files-with-command-copy-in-a-batch-file-introduce-an-incorrect-syn). I've lost an afternoon to it, so I hope this comment save you the hassle. – AXMIM Apr 05 '19 at 20:49
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. – AXMIM Apr 05 '19 at 21:39

MarioVW · Answer 3 · 2023-01-15T19:12:10.907

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.

SQLCMD include all scripts in folder

3 Answers3