Creating a view

This recipe shows how to create a view using PowerShell and SMO.

Getting ready

We will use the Person.Person table in the AdventureWorks2014 database for this recipe.

To give you an idea what we are attempting to create in this recipe, this is the T-SQL equivalent.

CREATE VIEW dbo.vwVCPerson
AS
SELECT
   TOP 100
  BusinessEntityID,
  LastName,
  FirstName
FROM
  Person.Person
WHERE
    PersonType = 'IN'
GO

How to do it...

Let's check out the steps to create a view using PowerShell:

1. Open PowerShell ISE as administrator.

2. Import the SQLPS module and create a new SMO Server object:

   #import SQL Server module
   Import-Module SQLPS -DisableNameChecking
   
   #replace this with your instance name
   $instanceName = "localhost"
   $server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $instanceName

   Add the following script and run:

   $dbName = "AdventureWorks2014"
   $db = $server.Databases[$dbName]
   $viewName = "vwVCPerson"
   $view = $db.Views[$viewName]
   
   #if view exists, drop it...