SQLCMD include all scripts in folder
Asked Answered
E

3

8

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 answered 13/3, 2014 at 14:29 Comment(3)
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
E
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.

Entice answered 31/3, 2015 at 6:35 Comment(4)
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:#27924073Entice
D
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.

Decipher answered 6/6, 2017 at 22:45 Comment(3)
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
N
0

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 answered 15/1, 2023 at 18:57 Comment(0)

© 2022 - 2025 — McMap. All rights reserved.