Powershell: I need help to modify a lengthy SQL script file

Question

I have a lengthy SQL script which contains MANY/multiple sections like this (amongst other script sections):

USE [NAVDB]
GO

IF NOT EXISTS (SELECT name FROM sys.database_principals 
               WHERE name = '@@Placeholder@@')  
    CREATE USER [MyDomain\adcsuser] 
    FOR LOGIN [MyDomain\adcsuser] 
    WITH DEFAULT_SCHEMA = [MyDomain\adcsuser]
GO

GRANT CONNECT TO [MyDomain\adcsuser] AS [dbo]
GO

I need to parse this script tile and modify only the IF NOT EXISTS...CREATE USER... lines of the script such that "@@Placeholder@@" is replaced by the text within the square brackets [] in the same line immediately following the CREATE USER string.

So, the line in the above snippet would become:

IF NOT EXISTS (SELECT name FROM sys.database_principals 
               WHERE name = 'MyDomain\adcsuser')  
    CREATE USER [MyDomain\adcsuser] 
    FOR LOGIN [MyDomain\adcsuser] 
    WITH DEFAULT_SCHEMA = [MyDomain\adcsuser]

The file is many hundreds of lines long with many dozen (at least) of these sections in it.

NotePad++ find-replace and macros couldn't handle it because of the "\" in the names between the []s and I couldn't find how to make NP++ copy the text between []s.

Also, I tried reviewing other related answers like: How can I replace every occurrence of a String in a file with PowerShell?, but remain stymied so far.

Because of the complex nature of the script file structure I'm leery of "read the whole file and just use Regex find/replace" approaches, so I was looking for a more... RBAR (Row By Agonizing Row) approach.

Dynamic SQL or parameterized approaches and similar suggestions are NOT APPROPRIATE for this situation as THE SCRIPT WAS ALREADY DYNAMICALLY GENERATED from an existing Production system (and I do not have the source code for the utility which generated this as output). I literally can't make wholesale structural changes like that.

Also, after reading this post once again, I should point out that the whole "IF NOT EXISTS...WITH DEFAULT_SCHEMA [*]" command is on ONE LINE in the script file (if that wasn't sufficiently clear).

Replacing seems like a bad idea. Parametrising your script would be a far better idea. Otherwise you're just opening yourself to the world on injection. — Thom A, Oct 29 '19 at 15:53
The script was generated from an existing SQL Server that is being upgraded into a new server. There is no realistic SQL injection risk here as this input does not come from anything "outside". Unfortunately, I don't have the source code for the tool that generates the script, so I can't fix this in the generation stage. — Mark Burns, Oct 29 '19 at 16:05
If you simple want to therefore replace the values have you had a look at https://stackoverflow.com/a/17144445/3484879 ? — Thom A, Oct 29 '19 at 16:21
Inadequate. You are missing the key idea here. Logically, I need to: 1) Find the lines in the script I need to change; 2) LOOK UP the Username in the retrieved line; 3) Make the substitution for @@Placeholder@@ with the discovered UserID string; 4) save the updated line back into the file (or into a new file) - along with all the other (skipped over) script line; 5) Lather, rinse, repeat as needed. — Mark Burns, Oct 29 '19 at 18:02
You can't do that; not without Dynamic SQL. And if that's the case, you might as well change your solution and parametrise it. — Thom A, Oct 29 '19 at 18:11
so... `MyDomain\adcsuse` is in 3 of the 4 lines -- it is just one of the lines that has the placeholder? — Hogan, Oct 29 '19 at 18:22
Clearly the correct way to do this is with dynamic sql/and or parameter based stored procedures -- so what if it was done before and lost -- just re-write it. Doing it the right way is always the right way to do it. — Hogan, Oct 29 '19 at 18:25
Going back to the Notepad++ option, replace @@Placeholder@@ with MyDomain\\adcsuser using the Search Mode Extended option in the replace screen. Or use the Normal mode and a single \. — UnhandledExcepSean, Oct 29 '19 at 18:30
Can't do that either "MyDomain/adcsuser" is just one of 150+ different usernames (some of which do and other that do not have the "MyDomain\" part of their name) that need to be substituted for the @@Placeholder@@ string. — Mark Burns, Oct 29 '19 at 18:37
Notepad++ on replace screen enter "CREATE USER \\[.*\\]" without quotes in the find and in replace put "CREATE USER \\[@@Placeholder@@\\]" without quotes. Make sure to select "Regular expression" under search mode. That should do it. — Tim Mylott, Oct 29 '19 at 19:44
Notepad++ support regex in it's search. Just to expand on my last comment. The .* is wildcard, brackets are special characters in regex so to include them as part of the search you have to escape them. So \\[.*\\] would find anything wrapped in brackets. I just added CREATE USER as an example on finding all those specific lines. — Tim Mylott, Oct 29 '19 at 19:56
Tim Mylott - Create an answer, so I can give you credit for it. — Mark Burns, Oct 29 '19 at 20:46
The NP++ Search was to use this pattern in the Find box: (?<=\[).+?(?=\]) From there it was a matter of recording a macro with careful steps, and Voila! script fixed. (I will post details on exactly what to do in NP++ to achieve this when Tim posts an answer.) However, I maintain that there IS a powershell way to achieve this result also. — Mark Burns, Oct 29 '19 at 20:52

Tim Mylott · Accepted Answer · 2019-10-30T17:00:21.827

For NotePad++ the find and replace support regex.

Example of how to find and replace all lines containing "CREATE USER [someusername]" with your replacement @@Placeholder@@ would be:

The .* is wildcard, brackets are special characters in regex so to include them as part of the search you have to escape them. So \[.*\] would find anything wrapped in brackets. I just added CREATE USER as an example on finding all those specific lines.

Making sure to select "Regular expression" in Search Mode.

PowerShell, with everything on one line, you can read in each line, find the match, extract the user and then replace; copying that line back out to a new file.

Input test file:

Example PowerShell script:

#create new output file
new-item "C:\temp\test2.txt" -Force
foreach ($line in (get-content "C:\temp\test.txt"))
{
    #Find the the user between the brackets and remove the brackets.
    $user = ([regex]"\[.*\]").Match($line).value.replace("[","").replace("]","")
    #Replace PlaceHolder with user pulled from the brackets and write to new file
    $line -replace '@@PlaceHolder@@', $user | out-file "C:\temp\test2.txt" -Append  
}

Then the contents of the output file:

Tim,Well you DID solve my problem, but your answer had it backwards. — Mark Burns, Oct 30 '19 at 13:13
@MarkBurns oh dang, you're right. I went back over and read the post again and I was totally off on what you were actually trying to accomplish. I updated the answer to give you a better example in powershell how to solve it. — Tim Mylott, Oct 30 '19 at 17:04

Mark Burns · Answer 2 · 2019-10-30T19:36:55.247

@Tim Mylott,

The entire issue arose from a script that was generated off my production system from Powershell's DBATools' Export-DbaLogin command.

The output script from that tool was like your first text.txt file in that it issues blind CREATE USER commands without first testing to see if the user already existed.

This was inadequate for our needs (our production DB and system have been around for years and now has old, bad 'lint' all over the place in it), so I took the script output into Notepad++ and added the IF NOT EXIST... test logic (see the script in the original question for details) as a prefix to the CREATE USER commands in the script with a global search & replace. Only, I had to put @@Placeholder@@ in for the username in the SELECT's WHERE clause for the test.

This left me with a script where I had to replace the @@Placeholder@@ text with the actual username strings in the existing CREATE USER text on the same line in the script file.

The solution, in NP++, which you led me to, was to use Regex in the NP++ Search to select the userid string in the same line. From there, it was fairly easy and straightforward to use a NP++ macro recording to automate the search & replace.

First, I found online a regex to select the text between the first matching pair of []s, which is: (?<=[).+?(?=]) The NP++ script recording was basically this (start Macro recording):

Find:@@PlaceHolder@@ (non-regex search) [NP++ finds the next line to alter] [Home] (to find the beginning of the line) Find: (?<=[).+?(?=]) (regex search) [NP++ selects the UserID string in the CREATE USER part of the line) [Ctrl+C] (copy UserID string to clipboard once Find dialog is closed) [Home] (to find the beginning of the line again) Find:@@PlaceHolder@@ (non-regex search) [NP++ finds the @@Placeholder@@ text in the current line and selects it] [Ctrl+V] (paste in the UserID string from the clipboard) (this leaves the cursor on the same location where the corrected command can be visually verified as correct)

In NP++ Use the Play Macro button to manually replay this recorded macro a few times to assure the macro has it right... Then, use the play multiple times (until the end of file is reached), and Voila! The script file is all fixed up.

[Note: the DBATools GitHub now has 2 bug reports/requests to add this IF NOT EXISTS logic to the CREATE USER lines in the generated script, so this issue will eventually go away, but for now... there is at least one reasonable solution using NotePad++.]

So, Thank You, Tim, for leading me to a quick answer.

That said, though, your Powershell part of the answer was wrong, so no points there! (it was doing the wrong thing and putting "@@Placeholder@@" INTO the script, rather than replacing the existing "@@PlaceHolder@@" string in the script with the actual UserID strings already IN the script.

To repeat, the problem was (logically) to go:

-- FROM THIS:
IF NOT EXISTS (SELECT ... WHERE name = '@@PlaceHolder@@')  CREATE USER [MyDomain\AUser2]...
IF NOT EXISTS (SELECT ... WHERE name = '@@PlaceHolder@@')  CREATE USER [MyDomain\AUser3]...
IF NOT EXISTS (SELECT ... WHERE name = '@@PlaceHolder@@')  CREATE USER [MyDomain\AUser5]...
IF NOT EXISTS (SELECT ... WHERE name = '@@PlaceHolder@@')  CREATE USER [MyDomain\AUser1]...
IF NOT EXISTS (SELECT ... WHERE name = '@@PlaceHolder@@')  CREATE USER [MyDomain\AUser7]...
IF NOT EXISTS (SELECT ... WHERE name = '@@PlaceHolder@@')  CREATE USER [MyDomain\AUser8]...
IF NOT EXISTS (SELECT ... WHERE name = '@@PlaceHolder@@')  CREATE USER [MyDomain\AUserA]...
IF NOT EXISTS (SELECT ... WHERE name = '@@PlaceHolder@@')  CREATE USER [MyDomain\AUserB]...
IF NOT EXISTS (SELECT ... WHERE name = '@@PlaceHolder@@')  CREATE USER [MyDomain\AUserC]...


-- TO THIS:
IF NOT EXISTS (SELECT ... WHERE name = 'MyDomain\AUser2')  CREATE USER [MyDomain\AUser2]...
IF NOT EXISTS (SELECT ... WHERE name = 'MyDomain\AUser3')  CREATE USER [MyDomain\AUser3]...
IF NOT EXISTS (SELECT ... WHERE name = 'MyDomain\AUser5')  CREATE USER [MyDomain\AUser5]...
IF NOT EXISTS (SELECT ... WHERE name = 'MyDomain\AUser1')  CREATE USER [MyDomain\AUser1]...
IF NOT EXISTS (SELECT ... WHERE name = 'MyDomain\AUser7')  CREATE USER [MyDomain\AUser7]...
IF NOT EXISTS (SELECT ... WHERE name = 'MyDomain\AUser8')  CREATE USER [MyDomain\AUser8]...
IF NOT EXISTS (SELECT ... WHERE name = 'MyDomain\AUserA')  CREATE USER [MyDomain\AUserA]...
IF NOT EXISTS (SELECT ... WHERE name = 'MyDomain\AUserB')  CREATE USER [MyDomain\AUserB]...
IF NOT EXISTS (SELECT ... WHERE name = 'MyDomain\AUserC')  CREATE USER [MyDomain\AUserC]…

Revised Script from Tim's excellent start (version 2) :-)

    #create new output file
new-item "C:\temp\test2.sql" -Force
foreach ($line in (get-content "C:\temp\test.sql"))
{
    if ($line.Contains("@@Placeholder@@"))
    {
    #Find the the user between the brackets and remove the brackets.
    $user = ([regex]"\[.+?\]").Match($line).value.replace("[","").replace("]","")
    #Replace PlaceHolder with user pulled from the brackets and write to new file
    $line -replace '@@Placeholder@@', $user | out-file "C:\temp\test2.sql" -Append 
    }
    else
    {
        out-file  "C:\temp\test2.sql" -Append -InputObject $line
    }

}

...Had to tweak the Regex (to less permissive) and output all the rest of the script lines too.

Powershell: I need help to modify a lengthy SQL script file

2 Answers2