Big Data, Data Analytics and Data Science | BI-BIGDATA.com

Powershell Tutorial Part 1 – Powershell for Creating View

This article I’ll explain how to create SQL Server View using PowerShell and SMO. This PowerShell Script is compatible with PowerShell V3.0.

I’ll be using Production.ProductCategory, Production.ProductSubCategory and Production.Product tables to create view. Following t-SQL script is equivalent script which we will write in PowerShell later.

CREATE VIEW dbo.vwProducts_Details
AS
SELECT
P.ProductID,
P.Name,
P.ProductNumber,
P.Color,
PC.Name AS ProductCategory,
PSC.Name AS ProductSubcategory,
P.StandardCost
FROM
Production.ProductCategory AS PC
INNER JOIN Production.ProductSubcategory AS PSC
ON PC.ProductCategoryID = PSC.ProductCategoryID
INNER JOIN Production.Product AS P
ON PSC.ProductSubcategoryID = P.ProductSubcategoryID
WHERE
P.StandardCost > 1000
GO

Here is PowerShell Script to create View

#import SQL Server module to create a new SMO Server object
Import-Module SQLPS -DisableNameChecking

#your SQL Server Instance Name
$SQLInstanceName = "SQL2012"
$Server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $SQLInstanceName

#provide your database name where you want to create view
$DatabaseName = "AdventureWorks2008R2"

#create SMO handle to your database
$DBObject = $Server.Databases[$DatabaseName]

#view name
$vwName = "vwProducts_Details"

#create SMO handle for your view
$vwObject = $DBObject.Views[$vwName]

#check if view exists, if exists then drop it
if ($vwObject)
{
$vwObject.Drop()
}

#Create an SMO View handle, which requires three parameters (database handle, view name, and schema)
$vwObject = New-Object -TypeName Microsoft.SqlServer.Management.SMO.View -ArgumentList $DBObject, $vwName, "dbo"

#TextMode = false  means no need to explicitly write the CREATE VIEW header
$vwObject.TextMode = $false

#TextMode is false means we can provide only view body with Select Statement
$vwObject.TextBody = @"
SELECT
P.ProductID,
P.Name,
P.ProductNumber,
P.Color,
PC.Name AS ProductCategory,
PSC.Name AS ProductSubcategory,
P.StandardCost
FROM
Production.ProductCategory PC
INNER JOIN Production.ProductSubcategory PSC
ON PC.ProductCategoryID = PSC.ProductCategoryID
INNER JOIN Production.Product P
ON PSC.ProductSubcategoryID = P.ProductSubcategoryID
WHERE
P.StandardCost > 1000
“@

$vwObject.Create()

We have implemented one property called TextMode you can set value of TextMode to either true or false.  If you set the TextMode property to true, it means you have to define the view’s TextHeader property as below

$vwObject.TextMode = $true
$vwObject.TextHeader = "CREATE VIEW dbo. vwProducts_Details AS "

You can use your SSMS to verify the view created. Connect to your database and expand the views you will notice that view “vwProducts_Details” is created. Now here is code snippets in PowerShell to execute the view.

#your SQL Server Instance Name
$SQLInstanceName = "SQL2012"
#provide your database name where you want to execute your view/sql command
$DatabaseName = "AdventureWorks2008R2"
#Invoke SQL to execute SQL Queries
$ResultSet = Invoke-Sqlcmd -Query "SELECT * FROM dbo.vwProducts_Details" -ServerInstance $SQLInstanceName -Database $DatabaseName

$ResultSet | Format-Table –AutoSize

Hope creating view with PowerShell without using SSMS will help you guys. Do let us know your feedback for this Powershell tutorial.

Sandip (Powershell Expert)