swirl
Home Software Blog Wallpapers Webtools
Sql developer PowerShell cmdlets
Saturday 08, June 2019   |   Post link   |   Sample code  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
Sample code  Download related material


Comments

Posts By Year

2019 (5)
2018 (8)
2017 (11)
2016 (6)
2015 (18)
2014 (2)
2013 (4)
2012 (2)

Posts By Category

.NET (1)
.NET Core (2)
ASP.NET MVC (4)
AWS API Gateway (1)
Android (1)
Azure (2)
Book review (3)
Business (1)
C# (3)
C++ (1)
Containers (3)
Corporate culture (1)
Database (1)
Database migration (1)
Desktop (1)
DotNet (2)
Entity Framework (3)
Git (2)
IIS (1)
Java (2)
Life (6)
Linux (1)
Lucene (1)
OData (1)
Office (1)
PHP (1)
PowerShell (2)
Programming (21)
Rants (5)
SQL (1)
Software Engineering (1)
Software development (1)
Solr (1)
Sql Server (2)
Storage (1)
T-SQL (1)
TDD (1)
TSQL (5)
Tablet (1)
Technology (1)
Test Driven (1)
Unit Testing (1)
Utilities (2)
VC++ (1)
Visual Studio (2)
Wallpapers (1)
Windows (8)
XML (2)

Posts By Tags

.NET(3) API Gateway(1) ASP.NET(4) AWS(1) Adults(1) Advertising(1) Android(1) Anti-forgery(1) Azure(2) Backup(1) Beliefs(1) Book review(2) Books(1) Busy(1) C#(3) C++(3) CLR(1) CORS(1) CSRF(1) CTE(1) Callbacks(1) Checkbox(1) Cmdlet(1) Commons(1) Company culture(1) Consumerism(1) Containers(3) Core(2) DPI(1) Data-time(1) Database(1) Debugging(1) Delegates(1) Developer(2) Dockers(2) EF 1.0(1) Entity framework(1) Events(1) File copy(1) File history(1) Font(1) Git(2) GradleApache(1) HierarchyID(1) IIS(1) Installing(1) Intelli J(1) JSON(1) JavaScript(1) Linux(1) Localization(1) Log4J(1) Lucene(1) MVC(4) Management(2) Migration history(1) Mobile Apps(1) Modern Life(1) Money(1) OData(1) OPENXML(1) Objects(1) Office(1) Organization(1) PHP(1) Paths(1) PowerShell(2) Programming(1) Python(1) Quality(1) Runtimes(1) SD card(1) SQL(2) SQL Code-first Migration(1) SSH(1) Sattelite assemblies(1) School(1) Self reliance(1) Shell(1) Solr(1) Sony VAIO(1) Spirituality(1) Sql Express(1) System Image(1) TDD(1) TSQL(3) Table variables(1) Tables(1) Tablet(1) Url rewrite(1) Validation(2) Wallpaper(1) Wallpapers(1) Web Development(4) Windows(2) Windows 10(1) Windows 2016(2) Windows 8.1(1) Work culture(1) XML(1) Yii(1)