Generate SQL scripts in powershell

Question

I want to be able to generate simple repetitive scripts in SQL using Powershell as the scripting language. For example, If I want to generate a SQL GRANT command for every table in a database, and for every category of user in the database, the generic grant command might look like this:

grant $privs 
 on $table 
 to $user;

Here, $privs, $table, and $user are parameters to the Grant command, expressed as powershell variables.

I want to step through a list of data that could be applied to this generic form to produce the actual script. The list of data might look like this:

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

This should generate five SQL GRANT commands.

Full Disclosure: I am asking this question so as to be able to provide my own answer.

Answer 1

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.