Day: February 14, 2013

Script to Find Indexed Views

This script was precipitated by the bug about using indexed views and the MERGE statement.  I don’t use the MERGE much, as it makes my brain hurt, but they are used quite a bit at the company I work for.  Hence, I thought I would whip up a quick script to see if we were even using any indexed views.  As always, this script uses SQLPSX.

Import-Module SqlServer
$outputs = @();

get-sqldatabase -sqlserver 'YourServerName' | %{
	Get-SqlView -Database $_ | where{$_.HasIndex} | %{
		$output = New-Object -TypeName PSObject -Property @{
			DatabaseName = $_.Parent.Name
			ViewName = $_.Name
		}
		$outputs += $output
	}
}
$outputs | Format-Table -AutoSize