Welcome to Day 17 of my “A Month of PowerShell” series. This series will use the series landing page on this blog at //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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
#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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
#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:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
IF (($Object)) { $Object.TextBody = @' SELECT TestTableID, LastUpdatedDT, LastUpdatedBy, RowGuid, IsValid FROM Common.TestTable; '@ $Object.Alter() } |
To:
|
1 |
IF (($Object)) {$Object.Drop()} |
