PowerShell Script to Update Text Files
Posted on 2014-09-16
I have a directory with well over 200 SQL scripts. Unfortunately, not all of the scripts end with a 'GO' statement.
I would like a simple script that will do two things:
1) If the last 'line' fo code is not 'GO', add it to the file, terminated with a CRLF (`r`n)
2) if there is a 'GO' and the line is not terminated, add CRLF.
The last line should be determined as anything without whitespace, since I have found some files that contain GO`r`n`r`n`r`n`r`n, in which case, leave the file as is, becasue it ends with GO followed by at least one CRLF.
The purpose for this is each file will generate a stored procedure. We have many clients who can pick and chise which procedures they want included in their 'package'. The current process is to open each one individually and execute in SSMS. A much easier procedure would be to do a multi-select of the files they want from the template directory, copy them into a temp directory and a 'COPY *.* all.sql /a' to concatenate all the files together, then execute one script.
Since not all are terminated with a GO + CRLF, the code 'runs on' In the case of a GO without CRLF at the end of the line, you end up with: 'GOCREATE PROC ...' which produces an error.
In the case of those without a GO, CREATE PROCEDURE has to be the first statement in a batch, so an error is generated.
I do not need (or want to) make changes up any file that has a GO followed by one or more CRLF's.
Also, the largest file is about 14K.