By: Johan Bijnens | Updated: 2015-09-24 | Comments (1) | Related: > PowerShell
Problem
Every once in a while, I'm confronted with SQL Server databases that contain
invalid VIEW declarations. In Dev and QA environments this may not be a big
issue, since these environments are not used for production purposes. But in production environments I prefer
that such VIEWs be taken care of ASAP, because
every alert encountered during a production implementation will take time to
investigate, causing the final 'GO' to be delayed.
Solution
In this solution I'm using the PowerShell 4.0 with module SQLPs as provided with SQL Server 2014. Using the PowerShell module SQLPS and processing the SQL Server SMO (SMO) View objects provide a simple, but not so straight forward solution to detect such invalid VIEWs. Identifying invalid VIEWs will help development teams take corrective measures as well as help the DBAs by avoiding spending unnecessary time fixing the issue.
Lessons Learned from sp_refreshview to Correct SQL Server Views
In my first solution to this problem I was using the SQL Server provided
stored procedure sp_refreshview. At first, this was working just fine and performed as
expected in DEV and QA. After the first use in production, in
our case +100 instances, phone calls started coming in. Applications were
throwing exceptions due to invalid columns! It took a while before we
related this to our "harmful" operation of using sp_refreshview. We noticed that most views
for
a given database were altered at about the same time.
Apparently sp_refreshview may alter the
modify_date
( sys.views
). Search engines quickly pointed me to this article:
"Renamed
views under SQL Server Management Studio and sp_refreshview problems" which describes the problem that bit us big time.
The problem we encountered was that by using the SSMS object browser to modify the name of a VIEW this process only alters the name, but it does not alter the VIEW definition (DDL) stored in the system catalog. So when we used sp_refreshview it totally messed things up because of this. It's a good thing we script out all of our databases each week. We made an inventory of all the VIEWs that had been renamed and checked and repaired the original views. I've put this paragraph in here, to avoid others having to suffer the same burden!
PowerShell Script to Correct SQL Server View Renames
For this solution I've prepared a PowerShell script to handle the functionality as follows:
- Connect to a given database on a specified SQL Server Instance
- Iterate over all appropriate views
- Validate the views
- Detect invalid views
- Rename invalid view (if specified)
The parameters provided are shown below. $SQLInstance and $DbName are mandatory and the parameters to request the renaming of invalid VIEWs are optional and have been provided with default values.
[parameter(Mandatory=$true,HelpMessage="Database name")] [string]$DbName,
[parameter(Mandatory=$false,HelpMessage="Rename View False/True")] [switch]$RenameVw = $false,
[parameter(Mandatory=$false,HelpMessage="Suffix to be added to View Name")] [string]$Suffix = 'NotProvided'
)
This code takes care of the rename by adding a suffix if needed:
if ( $RenameVw ) {
#Has a suffix been given ?
if ( $Suffix -in '', 'NotProvided' ) {
$Suffix = get-date -Format 'yyyyMMdd' ;
}
}
This connects to the database:
$db = Get-SQLDatabase -serverinstance $SQLInstance -name $DbName -erroraction stop ;
Adding the parameter -ErrorAction Stop to a Get-SQLDatabase cmdlet causes an error to be passed through to the script. In this script it will cause the Trap command to kick in and interrupt further processing.
Select the views to be processed:
- no need to check system views
- no need to check schema bound views ( as they will prevent the objects on which they depend being modified if the modification would invalidate the view )
- do not process views already marked as processed
Keep in mind, this will result in an array of SMO VIEW objects!
# don't need to check system objectes, schema bound views or views already marked obsolete
$TargetVw += $db.Views | where { $_.IsSystemObject -eq $false -and $_.isIsSchemaBound -ne 1 -and $_.name -notlike '*OBSOLETE*' } | sort schema, name ;
Process the selected views.
As we don't want the script to get interrupted whenever it encounters an invalid view, this part is encapsulated with a try-catch construct. The $db.ExecuteNonQuery will fail whenever the given view is invalid and then we will handle that view in the catch.
Note: SMO seems to lack a method that is equivalent to the
SQL Server stored procedure
sp_refreshview! As mentioned in the first
paragraph of the solution, DO NOT USE
sp_refreshview!
Write-verbose $('Refreshing view ''[{0}].[{1}]'' in db [{2}].' -f $vw.Schema, $vw.Name, $vw.Parent.Name );
# ReCompileReferences IS NOT REFRESHVIEW !
#$vw.ReCompileReferences() ;
#$q = $('exe-DONTUSE-c sp_refreshview @viewname = ''[{0}].[{1}]'' ' -f $vw.Schema, $vw.Name );
#$db.ExecuteNonQuery( $q ) ;
...
}
catch {
.....
}
After the mishap described earlier, I investigated all data that sys.views provides and noticed it has a separate columns that contain the view header ( i.e. only the "create view ... as"-part) and the view body. Furthermore, I encapsulated the view validation in a transaction, always performing a ROLLBACK so I don't mess up the catalog modify_date data in sys.views. Views DDL needs to have their own batch operation, so you have to actually perform 3 database requests to handle it all correctly.
...
$IsRenamedView = $false ;
$crit = $('*create*view*{0}*' -f $vw.name )
if ( $vw.TextHeader -notlike $crit) {
$ProblemVwHeaders += $vw | Select schema, name, textheader, @{n='IsRenamedView';e={if ( $vw.TextHeader -notlike "*$($vw.name)*" ) { 1 } else {0}}
}
$Q = '--RENAMED-VIEW--'
$IsRenamedView = $true ;
Write-verbose $('Renamed view Detected [{0}].[{1}]' -f $vw.Schema, $vw.Name );
#Interrupt processing for remamed views
throw $vw.name ;
}
else {
# Assemble Alter view ensuring the correct view name by replacing the header info !
# if someone renamed a view by shortening it, that will not be detected by the previous IF !!
$q = $('Alter view [{0}].[{1}] as {2} /* DBA Detect Invalid Views */ ' -f $vw.Schema, $vw.Name, $vw.TextBody ) ;
$db.ExecuteNonQuery( 'Begin Transaction' )
$db.ExecuteNonQuery( $q ) ;
try {
# rollback the operation without causing the original catch to kick in.
$db.ExecuteNonQuery( 'Rollback Transaction' ) ;
}
catch {
Write-warning 'trx rollback failed' ;
}
}
}
catch {
.....
}
Handling the catch for failed "alter view" or renamed views:
- First of all we will write a warning on the console to notify that the view is invalid.
- If requested ( $RenameVw switch used ) rename the view:
- signal the rename action
- rename the view (using its own try-catch construct to prevent the script from being interrupted when the rename itself fails).
As you may have noticed, two counter variables are being used,
just to keep track of the number of invalid and renamed views.
...
}
catch {
$nInvalid ++;
write-warning $('Refresh view failed for ''[{0}].[{1}]'' ' -f $vw.Schema, $vw.Name );
if ( $RenameVw ) {
$NewName = $('{0}_OBSOLETE_{1}' -f $vw.Name, $Suffix) ;
write-output $('Renaming view [{0}].[{1}] to [{2}] ' -f $vw.Schema, $vw.Name, $NewName );
try {
$vw.Rename( $NewName ) ;
$nRenamed ++ ;
}
catch{
write-error $('Rename view failed for ''[{0}].[{1}]'' - [{2}] ' -f $vw.Schema, $vw.Name, $NewName );
}
}
}
Finally the script provides an overview of its processing actions. In this
case it will return a row of data (piping 1 to the SELECT clause) or a row per
invalid or renamed view.
# In stead of returning a single row (with one column holding an array of invalid views),
# just return total information and a single row for each invalid view or renamed view.
if ( $InvalidVw.count -gt 0 ) {
$InvalidVw | Select @{n='SQLInstance';e={$SQLInstance}}, @{n='DbName';e={$DbName}}, @{n='ViewsProcessed';e={$TargetVw.count}}, @{n='nInvalidViews';e={$nInvalid}}, @{n='nViewsRenamed';e={$nRenamed}}, @{n='StartDateTime';e={$TsStart}}, @{n='EndDateTime';e={get-date}} , @{n='InvalidView';e={$_}} ;
}
else {
1 | Select @{n='SQLInstance';e={$SQLInstance}}, @{n='DbName';e={$DbName}}, @{n='ViewsProcessed';e={$TargetVw.count}}, @{n='nInvalidViews';e={$nInvalid}}, @{n='nViewsRenamed';e={$nRenamed}}, @{n='StartDateTime';e={$TsStart}}, @{n='EndDateTime';e={get-date}} , @{n='InvalidView';e={$InvalidVw}} ;
}
Executing the PowerShell Script
Executing the script in its detection mode:
Provides a result like this:
Executing the script in its detect and rename mode:
Provides a result like this:
The Final PowerShell Script
Here are all of the components discussed in the above script.
.SYNOPSIS
Detect invalid views in database
.DESCRIPTION
Detect invalid views in database and rename if switch provided
.PARAMETER
If bound parameters, no need to put them overhere
.NOTES
-Date 2015-06-17 - Author Bijnens Johan
.EXAMPLE
#inventory of invalid views
$ScriptPath = (Join-Path -Path ([system.environment]::GetFolderPath('MyDocuments')) -ChildPath 'Powershell\dev' )
Set-location $ScriptPath ;
Clear-Host
& '.\ALZDBA - detect and rename invalid views.ps1' -SQLInstance Server\Instance -DbName YourDatabase ;
.EXAMPLE
#inventory of invalid views and rename invalid views ( this will cause the CurrentDate being used as suffix )
$ScriptPath = (Join-Path -Path ([system.environment]::GetFolderPath('MyDocuments')) -ChildPath 'Powershell\dev' )
Set-location $ScriptPath ;
Clear-Host
& '.\ALZDBA - detect and rename invalid views.ps1' -SQLInstance Server\Instance -DbName YourDatabase -RenameVw
.EXAMPLE
#inventory of invalid views and rename invalid views providing a project suffix
$ScriptPath = (Join-Path -Path ([system.environment]::GetFolderPath('MyDocuments')) -ChildPath 'Powershell\dev' )
Set-location $ScriptPath ;
Clear-Host
& '.\ALZDBA - detect and rename invalid views.ps1' -SQLInstance Server\Instance -DbName YourDatabase -RenameVw -Suffix 'myProjectName'
#>
Param([parameter(Mandatory=$true,HelpMessage="SQLServer name")] [string]$SQLInstance,
[parameter(Mandatory=$true,HelpMessage="Database name")] [string]$DbName,
[parameter(Mandatory=$false,HelpMessage="Rename View False/True")] [switch]$RenameVw = $false,
[parameter(Mandatory=$false,HelpMessage="Suffix to be added to View Name")] [string]$Suffix = 'NotProvided'
)
# Check module SQLPS
if ( !(get-module -name SQLPs ) ) {
# save original location
Push-Location
# SQLPs will set the current location to SQLSERVER:\ !!
# -DisableNameChecking -> avoid remarks abount non-discouverable function names
import-module -name SQLPs -DisableNameChecking | out-null
#reset current location to original location
Pop-Location
}
Trap {
# Handle the error
$err = $_.Exception
#Want to save tons of time debugging a #Powershell script? Put this in your catch blocks:
$ErrorLineNumber = $_.InvocationInfo.ScriptLineNumber
write-warning $('Trapped error at line [{0}] : [{1}]' -f $ErrorLineNumber, $err.Message );
write-Error $err.Message
while( $err.InnerException ) {
$err = $err.InnerException
write-error $err.Message
};
# End the script.
break
}
if ( $RenameVw ) {
if ( $Suffix -in '', 'NotProvided' ) {
$Suffix = get-date -Format 'yyyyMMdd' ;
}
}
$TsStart = get-date ;
$db = get-sqldatabase -serverinstance $SQLInstance -name $DbName -erroraction stop ;
$ProblemVwHeaders = @();
$ErrRB = '';
#Clear-Host
if ( $db.status -eq 'Normal' -and $db.IsAccessible -eq $true -and $db.IsDatabaseSnapshot -eq $false -and $db.IsUpdateable -eq $true -and $db.ReadOnly -eq $false ) {
$TargetVw = @() ;
$InvalidVw = @() ;
write-host $('Processing views of [{0}] - [{1}]' -f $SQLInstance, $DbName )
# don't need to check schema bound views
$TargetVw += $db.Views | where { $_.IsSystemObject -eq $false -and $_.isIsSchemaBound -ne 1 -and $_.name -notlike '*OBSOLETE*' } | sort schema, name ;
$nInvalid = 0 ;
$nRenamed = 0 ;
$xCtr = 0 ;
foreach ( $vw in $TargetVw ) {
$xCtr ++ ;
$pct = $xCtr * 100 / $TargetVw.count
Write-Progress -Activity $( 'Refreshing views of [{0}] - [{1}]' -f $SQLInstance, $DbName ) -Status $( 'Progressing [{0}].[{1}]' -f $vw.schema, $vw.Name ) -PercentComplete $pct
try {
Write-verbose $('Refreshing view [{0}].[{1}] in db [{2}].' -f $vw.Schema, $vw.Name, $vw.Parent.Name );
# ReCompileReferences IS NOT REFRESHVIEW !
#$vw.ReCompileReferences() ;
$IsRenamedView = $false ;
$crit = $('*create*view*{0}*' -f $vw.name )
if ( $vw.TextHeader -notlike $crit) {
$ProblemVwHeaders += $vw | Select schema, name, textheader, @{n='IsRenamedView';e={if ( $vw.TextHeader -notlike "*$($vw.name)*" ) { 1 } else {0}}}
$Q = '--RENAMED-VIEW--'
$IsRenamedView = $true ;
Write-verbose $('Renamed view Detected [{0}].[{1}]' -f $vw.Schema, $vw.Name );
throw $vw.name ;
}
else {
#$q = $('exe-DoNotUse-c sp_refreshview @viewname = ''[{0}].[{1}]'' ' -f $vw.Schema, $vw.Name );
#write-host 'Do not use SP_REFRESHVIEW -> see mssqltips artilce !' -BackgroundColor Red -ForegroundColor Black ;
# Assemble Alter view ensuring the correct view name by replacing the header info !
$q = $('Alter view [{0}].[{1}] as {2} /* DBA Detect Invalid Views */ ' -f $vw.Schema, $vw.Name, $vw.TextBody ) ;
Write-verbose $('Refreshing view [{0}].[{1}] Begin Tran' -f $vw.Schema, $vw.Name );
$db.ExecuteNonQuery( 'Begin Transaction' )
Write-verbose $('Refreshing view [{0}].[{1}] Alter' -f $vw.Schema, $vw.Name );
$db.ExecuteNonQuery( $q ) ;
try {
Write-verbose $('Refreshing view [{0}].[{1}] Rollback tran' -f $vw.Schema, $vw.Name );
$db.ExecuteNonQuery( 'Rollback Transaction' ) ;
}
catch {
$ErrRB = $_ ;
Write-Verbose 'trx rollback failed' ;
}
}
}
catch {
$Err = $_ ;
$nInvalid ++;
if ( $db.parent.ConnectionContext.TransactionDepth -gt 0 ) {
try {
$db.ExecuteNonQuery( 'Rollback Transaction' ) ;
}
catch {
Write-Verbose 'trx rollbacked failed - cleanup' ;
}
}
write-warning $('Refresh view failed for [{0}].[{1}]' -f $vw.Schema, $vw.Name );
$InvalidVw += $('{0} [{1}].[{2}] - {3} - {4}' -f $( if ($IsRenamedView) {'**RenamedView**'}else{'
$NewName = $('{0}_OBSOLETE_{1}' -f $vw.Name, $Suffix) ;
if ( $RenameVw ) {
write-output $('Renaming view [{0}].[{1}] to [{2}] ' -f $vw.Schema, $vw.Name, $NewName );
try {
#Rename the view the proper way !
$vw.Rename( $NewName ) ;
$nRenamed ++ ;
}
catch{
write-error $('Rename view failed for [{0}].[{1}] - [{2}] ' -f $vw.Schema, $vw.Name, $NewName );
}
}
}
}
# Hand over the results
# In stead of returning a single row (with one column holding an array of invalid views),
# just return total information and a single row for each invalid or renamed view.
if ( $InvalidVw.count -gt 0 ) {
$InvalidVw | Select @{n='SQLInstance';e={$SQLInstance}}, @{n='DbName';e={$DbName}}, @{n='ViewsProcessed';e={$TargetVw.count}}, @{n='nInvalidViews';e={$nInvalid}}, @{n='nViewsRenamed';e={$nRenamed}}, @{n='StartDateTime';e={$TsStart}}, @{n='EndDateTime';e={get-date}} , @{n='InvalidView';e={$_}} ;
}
else {
1 | Select @{n='SQLInstance';e={$SQLInstance}}, @{n='DbName';e={$DbName}}, @{n='ViewsProcessed';e={$TargetVw.count}}, @{n='nInvalidViews';e={$nInvalid}}, @{n='nViewsRenamed';e={$nRenamed}}, @{n='StartDateTime';e={$TsStart}}, @{n='EndDateTime';e={get-date}} , @{n='InvalidView';e={$InvalidVw}} ;
}
}
else {
write-warning $('Database not valid for Invalid View detection [{0}] - [{1}] ' -f $SQLInstance, $DbName );
}
Write-host 'The end' -BackgroundColor Green -ForegroundColor Black ;
Conclusion
As shown, SQLPs exposes SQL Server SMO objects which are often provided with methods and properties that can make your life easier as a SQL Server DBA. Don't just rush into solutions until you understand the implications as I found using sp_refreshview.
I hope you enjoyed this little read on yet another quest for managing SQL Server instances.
Next Steps
- Test the script, inspect its results
- Enjoy the marvels of PowerShell with these other PowerShell tips
About the author
Johan Bijnens is a database firefighter, working with SQL Server since 2000, working at Aperam Genk.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2015-09-24
