Detect and rename invalid VIEWs in a SQL Server database using PowerShell


By:   |   Updated: 2015-09-24   |   Comments (1)   |   Related: More > 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.

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'
)

This code takes care of the rename by adding a suffix if needed:

#Rename view requested ?
if ( $RenameVw ) {
#Has a suffix been given ?
if ( $Suffix -in '', 'NotProvided' ) {
$Suffix = get-date -Format 'yyyyMMdd' ;
}
}

This connects to the database:

#Connect 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!

$TargetVw = @() ;
# 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!

try {
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.

try {
...
$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.

try {
...
}
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.

#Report processed numbers
# 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:

Detection mode

Provides a result like this:

Detect invalid view

Executing the script in its detect and rename mode:

Detection and rename mode

Provides a result like this:

Detect and Rename invalid views

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{''}), $vw.Schema, $vw.Name, $vw.TextHeader, (get-date -Format 'yyyy-MM-dd HH:mm:ss')) ;

$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


Last Updated: 2015-09-24


get scripts

next tip button



About the author
MSSQLTips author Johan Bijnens Johan Bijnens is a database firefighter, working with SQL Server since 2000, working at Aperam Genk.

View all my tips
Related Resources





Comments For This Article




Thursday, September 24, 2015 - 3:23:39 AM - alzdba Back To Top (38744)

FYI the referred SSMS flaw does not occur with SQLServer 2014 SSMS !



download





Recommended Reading

Setting the PowerShell Execution Policy

How to find a specific text string in a SQL Server Stored Procedure, Function, View or Trigger

Using PowerShell to Work with Directories and Files

Bulk Copy Data from Oracle to SQL Server

Execute SQL Server Stored Procedures from PowerShell








get free sql tips
agree to terms


Learn more about SQL Server tools