Advertisements
Home > Powershell, SQL Server > PowerShell tutorial Part 1- Poweshell for Creating View

PowerShell tutorial Part 1- Poweshell for Creating View

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)

Advertisements
  1. vps
    March 14, 2013 at 10:10 pm

    I think your blog is amazing. You write about very interesting things. Thanks for all the tips and information.

  2. April 7, 2013 at 11:48 am

    Spot on with this write-up, I truly assume this website wants far more consideration. I’ll probably be again to read far more, thanks for that info.

  3. April 11, 2013 at 2:28 am

    Can I just say what a relief to find someone who actually is aware of what theyre talking about on the internet. You positively know find out how to bring a difficulty to mild and make it important. Extra people must read this and perceive this aspect of the story. I cant believe youre no more common since you positively have the gift.

  4. April 11, 2013 at 11:45 pm

    It’s hard to seek out knowledgeable folks on this subject, but you sound like you recognize what you’re talking about! Thanks

  5. April 12, 2013 at 11:53 pm

    Wonderful goods from you, man. I have understand your stuff previous to and you are just too wonderful.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: