Sql developer PowerShell cmdlets

Saturday 08, June 2019   |   Post link   |     Download related material

Often one needs to define a SQL table or a table variable based on the output of a query. This set of PowerShell cmdlets let's you do just this. The two PowerShell commands Get-TableSql and Get-TableVariableSql allow you to generate a table or table variable definition from the output of a query. For example, its possible to generate the following SQL:

create table #TempTable 
( 
  BusinessEntityId int CONSTRAINT MyPK PRIMARY KEY NOT NULL, 
  FirstName nvarchar(50) NOT NULL, 
  MiddleName nvarchar(50) NULL, 
  LastName nvarchar(50) NOT NULL, 
  ModifiedDate datetime NOT NULL 
)

by invoking the Get-TableSql cmdlet with like this:

  Get-TableSql -ConnectionString "Server=localhost\SQL2017;Integrated Security=true;Initial Catalog=AdventureWorks2017" -Query "select BusinessEntityId, FirstName, MiddleName, LastName, ModifiedDate from Person.Person;" -OutputName "#TempTable" -GeneratePrimaryKey -PrimaryKeyName MyPK

How to use

Firstly, you need to either download the cmdlet binaries by clicking the 'Download Related Material' link at the top of the page or download the source from GitHub and build the project.

Next, open PowerShell and load the module using the following command:

Import-Module .\SqlDevCmdlets.dll

Viewing the help

Once the module is loaded, type in:

Get-Help Get-TableSql -full
Get-Help Get-TableVariableSql -full

Categories: PowerShell (2) Sql Server (2)

Tags: Table variables(1) Tables(1)