Search code examples
t-sqlpowershell-4.0

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

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

Solution

  • 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:

    enter image description here

    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: enter image description here

    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:

    enter image description here