1

I've got a maintenance script that is generating .sql files with some SQL script in there.

When the file size reaches a limit, I close the file and create a new one, dumping the data into the 2nd file.

I have no idea how many files will be generated (it's based upon the amount of data at the current time). So far it looks like it's about 100 files or so.

So, is there a nice way I can say

  • Get all the files that end with .sql from a target directory.
  • now run this command for each one.

The command I run in the Command window on my SQL Server is this: 

sqlcmd -S 127.0.0.1 -d SomeDBTenant -U someUser -P somePassword -I -i c:\temp\SqlScript_1.sql
Ansgar Wiechers
  • 193,178
  • 25
  • 254
  • 328
Pure.Krome
  • 84,693
  • 113
  • 396
  • 647
  • possible duplicate of [how to iterate over files with powershell?](http://stackoverflow.com/questions/3605522/how-to-iterate-over-files-with-powershell) – arco444 Aug 11 '15 at 09:20
  • I guess you do not really mean "DOS" but Windows command prompt, right? Please consider to adapt the question title accordingly... – aschipfl Aug 11 '15 at 10:56

2 Answers2

2

Yes, you can use the Get-ChildItem cmdlet to get all .sql files (add -recurseif you want to do it recursively) and run sqlcmd for each of them:

$sqlFiles = Get-ChildItem -Path 'C:\test' -Filter '*.sql' # you can do it recurse with -r
$sqlFiles | foreach { sqlcmd -S 127.0.0.1 -d SomeDBTenant -U someUser -P somePassword -I -i $_.FullName }
Martin Brandl
  • 56,134
  • 13
  • 133
  • 172
2

In Windows command prompt you can use for to walk through all files that match (a) certain pattern(s) in a directory or a directory tree, like:

rem remove the `/R` switch if you don't want to loop recursively
for /R %%F in (*.sql) do (
  REM replace `%%~F` by `%%~fF` to pass the file's full path to the command
  sqlcmd -S 127.0.0.1 -d SomeDBTenant -U someUser -P somePassword -I -i "%%~F"
)

Note: This only works when sqlcmd does not create/rename/delete any *.sql files in the enumerated directory. In addition, if the /R option is used, sqlcmd must not modify the enumerated directory tree.

aschipfl
  • 33,626
  • 12
  • 54
  • 99
  • 1
    If the `sqlcmd` is creating .sql files then the for command can re-process some .sql files a second time etc. Using the `for /f` form of the for command with dir will avoid this. – foxidrive Aug 11 '15 at 14:23
  • Good point, @foxidrive! I tried that once with a non-recursive `for`, it seems that `for` enumerates the directory content once; however, I did not try this with `/R` -- I'll do that the next days and correct my code in case... – aschipfl Aug 11 '15 at 16:57
  • That's a nice answer for the command prompt. BTW @foxidrive the `sqlcmd` executable is NOT creating the `.sql` files. something -else- does that ... `sqlcmd` just parses/reads the scripts (and executes them). – Pure.Krome Aug 11 '15 at 22:38
  • @Pure.Krome What filetype is your sqlcmd creating ? – foxidrive Aug 12 '15 at 00:54
  • It's _not_ creating anything. `sqlcmd.exe` comes with Sql Server for doing cli stuff. I have a different app that creates files (that end with `.sql`) and use those with sqlcmd. – Pure.Krome Aug 12 '15 at 01:44
  • @foxidrive, I decided to post a question concerning enumeration of directories whose contents change during that, using `for` -- see [this question](http://stackoverflow.com/q/31975093/5047996)... – aschipfl Aug 13 '15 at 09:56
  • @aschipfl Thanks for your message. Aacini is a smart fellow. – foxidrive Aug 13 '15 at 11:57