Generate SQL scripts in powershell

I have written a little function in Powershell that applies a driver table to a template in order to produce a simple repetitive result. It was really a learning exercise to help me become proficient in Powershell.

Here is the function:

 <#
.NOTES
    Script: Expand-Csv    Rev:  3.2
    By:     DGC           Date: 2-21-19
.SYNOPSIS
    Generates multiple expansions of a template,
    driven by data in a CSV file.
.DESCRIPTION
    This function is a table driven template tool. 

    It generates output from a template and
    a driver table.  The template file contains plain
    text and embedded variables.  The driver table 
    (in a csv file) has one column for each variable, 
    and one row for each expansion to be generated.
#>
function Expand-csv {
    [CmdletBinding()]
    Param (
       [Parameter(Mandatory=$true)]
       [string] $driver,
       [Parameter(Mandatory=$true)]
       [string] $template
    )
    Process {
       Import-Csv $driver | % {
           $_.psobject.properties | % {Set-variable -name $_.name -value $_.value}
           Get-Content $template | % {$ExecutionContext.InvokeCommand.ExpandString($_)} 
       }
    }
}

Expand-Csv takes two inputs. The first is a CSV file that represents the driver table. In this example, the csv file might look like this.

privs,table,user
ALL,Employees,DBA
READ,Employees,Analyst
"READ, WRITE", Employees, Application
ALL,Departments,DBA
READ,Departments,"Analyst, Application"

The template might look like this:

grant $privs 
   on $table 
   to $user;

The result of running it through Expand-Csv might look like this:

grant ALL 
   on Employees 
   to DBA;


grant READ 
   on Employees 
   to Analyst;


grant READ, WRITE 
   on Employees 
   to Application;


grant ALL 
   on Departments 
   to DBA;


grant READ 
   on Departments 
   to Analyst, Application;

This result is fed down the pipeline, but it could easily be written to a file for later execution by SQL.

A superior tool might be written that takes as input a table or view in a database instead of a .CSV file. I wanted to do the easy stuff first.

I apologize for the fact that the code is not easy to read. I was interested in minimizing the use of variables inside it, so I used $_ in more than one context. At least it's small.

You may be able to use this tool as a starting point to develop something that will make your workload easier. I hope so.