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

.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’={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,
$Suffixif (
$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’={$SQLInstance}},
@{n=‘DbName’={$DbName}},
@{n=‘ViewsProcessed’={$TargetVw.count}},
@{n=‘nInvalidViews’={$nInvalid}},
@{n=‘nViewsRenamed’={$nRenamed}},
@{n=‘StartDateTime’={$TsStart}},
@{n=‘EndDateTime’={get-date}}
, @{n=‘InvalidView’={$_}}
;
}

else {

1 |
Select @{n=‘SQLInstance’={$SQLInstance}},
@{n=‘DbName’={$DbName}},
@{n=‘ViewsProcessed’={$TargetVw.count}},
@{n=‘nInvalidViews’={$nInvalid}},
@{n=‘nViewsRenamed’={$nRenamed}},
@{n=‘StartDateTime’={$TsStart}},
@{n=‘EndDateTime’={get-date}}
, @{n=‘InvalidView’={$InvalidVw}}
;
}
}
else {

write-warning $(‘Database
not valid for Invalid View detection [{0}] – [{1}] ‘
-f
$SQLInstance,
$DbName }

Write-host
‘The end’
-BackgroundColor Green
-ForegroundColor
Black