http://www.sqlservercentral.com/blogs/discussionofsqlserver/2013/02/17/a-month-of-powershell-day-17-databases-views/

Printed 2014/04/24 10:10PM

A Month of PowerShell – Day 17 (Databases: Views)

By Wayne Sheffield, 2013/02/17

Welcome to Day 17 of my “A Month of PowerShell” series. This series will use the series landing page on this blog at http://blog.waynesheffield.com/wayne/a-month-of-powershell/. Please refer to this page to see all of the posts in this series, and to quickly go to them.

We’ve spent several days working with tables, but that’s not all that is in a database. Continuing the mini-series of objects compiled with T-SQL code, today we will work with views.

Creating a view

Source code   
#Assign variables
$Instance   = "localhost\SQL2012"
$DBName     = "PoSh"
$SchemaName = "Common"
$ObjectName = "vTestTable"
 
#Assign the SMO class to a variable
$SMO        = "Microsoft.SqlServer.Management.Smo"
 
# get the server
$Server = New-Object ("$SMO.Server") "$Instance"
 
# assign the database name to a variable
$MyDB = $Server.Databases[$DBName]
 
# assign the schema to a variable
$Schema = $MyDB.Schemas[$SchemaName]
 
# check to see if the view exists
$Object = $MyDB.Views.Item($ObjectName, $SchemaName)
 
IF (!($Object))
{
    $Object = New-Object ("$SMO.View") ($MyDB, $ObjectName, $SchemaName)
    $Object.TextHeader = "CREATE VIEW [$SchemaName].[$ObjectName] AS"
    $Object.TextBody = 'SELECT * FROM Common.TestTable;'
    $Object.Create()
}

That was pretty simple. Does anybody out there have any questions? Okay, let’s move on.

Altering a view

Perhaps you noticed that the view’s definition is performing a select *. Seeing this gets the fangs coming out, fingernails in the clawing position, and the hair rising on the back of my neck. Okay, not really, but it is a practice generally considered to be bad. Let’s change this view to specify the columns.

Source code   
#Assign variables
$Instance   = "localhost\SQL2012"
$DBName     = "PoSh"
$SchemaName = "Common"
$ObjectName = "vTestTable"
 
#Assign the SMO class to a variable
$SMO        = "Microsoft.SqlServer.Management.Smo"
 
# get the server
$Server = New-Object ("$SMO.Server") "$Instance"
 
# assign the database name to a variable
$MyDB = $Server.Databases[$DBName]
 
# assign the schema to a variable
$Schema = $MyDB.Schemas[$SchemaName]
 
# check to see if the view exists
$Object = $MyDB.Views.Item($ObjectName, $SchemaName)
 
IF (($Object))
{
    $Object.TextBody = @'
SELECT  TestTableID,
        LastUpdatedDT,
        LastUpdatedBy,
        RowGuid,
        IsValid
FROM    Common.TestTable;
'@
    $Object.Alter()
}

Dropping a view

If you want to drop the view, after verifying that the view does exist, simply call its drop method. So from the above script for the alter, change this section:

Source code   
IF (($Object))
{
    $Object.TextBody = @'
SELECT  TestTableID,
        LastUpdatedDT,
        LastUpdatedBy,
        RowGuid,
        IsValid
FROM    Common.TestTable;
'@
    $Object.Alter()
}

To:

Source code   
IF (($Object)) {$Object.Drop()}

Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.