Productivity Tools

Tools written by an IT business manager, a passionate developer.

Powershell SQL Commands

The module helps to perform basic operations on the SQL Server. It wraps SQL queries into methods making basic SQL operation more convenient.

Using this module instead of invoking query:

CREATE TABLE [schema].[TableName] (TableNameId INT IDENTITY(1,1) PRIMARY KEY

We can use the method

New-SQLTable -SqlInstance "localhost" -DatabaseName "TableName" -SchemaName "schema"

Methods are protected against invoking them twice. So before adding a column, check is performed if the column doesn’t already exist.

RequiredModules = @("SqlServer")

If you have installed previous version of the module from sql (SQLPS) during the installation error occur. (it could be checked using command Get-Module SQLPS)

Import-Module : The following error occurred while loading the extended type data file: Error in TypeData "Microsoft.SqlServer.Management.Smo.NamedSmoObject": The member DefaultKeyPropertySet is already present. Error in TypeData "Microsoft.SqlServer.Management.Smo.NamedSmoObject": The member DefaultDisplayPropertySet is already present. Error in TypeData "Microsoft.SqlServer.Management.Smo.NamedSmoObject": The member DefaultDisplayProperty is already present.

It can be easily fixed removing old module, the new one (SQLServer) replace old one. (http://www.mikefal.net/2016/07/12/out-with-the-sqlps-in-with-the-sqlserver/)

Remove-Module SQLPS  Import-Module SQLCommands

The following picture shows the possible option of the database creation with one table and one column

Powershell SQL Commands

Commands will result in the following structure:

Managment studio