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 AdventureWorks2008R2 database for this recipe.

To give you an idea of 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'
ORDER BY
    LastName
GO

How to do it...

1. Open the PowerShell console by going to Start | Accessories | Windows PowerShell | Windows PowerShell ISE.

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 = "KERRIGAN"
   $server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $instanceName

3. Add the following script and run it:

   $dbName = "AdventureWorks2008R2"
   $db = $server.Databases[$dbName]
   $viewName = "vwVCPerson"
   $view = $db...