IndexFragCheck.ps1

#Region Functions
function Export-Xls{
	<#
.SYNOPSIS
	Saves Microsoft .NET Framework objects to a worksheet in an XLS file
.DESCRIPTION
	The Export-Xls function allows you to save Microsoft .NET Framework objects
	to a named worksheet in an Excel file (type XLS). The position of the
	worksheet can be specified.
.NOTES
	Author:  Luc Dekens
.PARAMETER InputObject
	Specifies the objects to be written to the worksheet. The parameter accepts
	objects through the pipeline.
.PARAMETER Path
	Specifies the path to the XLS file.
.PARAMETER WorksheetName
	The name for the new worksheet. If not specified the name will
	be "Sheet" followed by the "Ticks" value
.PARAMETER SheetPosition
	Specifies where the new worksheet will be inserted in the series of
	existing worksheets. You can specify "begin" or "end". The default
	is "begin".
.PARAMETER ChartType
	Specifies the type of chart you want add to the worksheet.
	All types in the [microsoft.Office.Interop.Excel.XlChartType]
	enumeration are accepted.
.PARAMETER NoTypeInformation
	Omits the type information from the worksheet. The default is to
	include the "#TYPE" line.
.PARAMETER AppendWorksheet
	Specifies if the worksheet should keep or remove the existing
	worksheet in the spreadsheet. The default is to append.
.EXAMPLE
	PS> $data = Get-Process | Select-Object Name, Id, WS
	PS> Export-Xls $data C:\Reports\MyWkb.xls -WorksheetName "WS" -AppendWorksheet:$false
.EXAMPLE
	PS> $data = Get-Process | Select-Object Name, Id, WS
	PS> Export-Xls $data C:\Reports\MyWkb.xls -SheetPosition "end"
.EXAMPLE
	PS> $data = Get-Process | Select-Object Name, Id, WS
	PS> Export-Xls $data C:\Reports\MyWkb.xls -WorksheetName "WS" -ChartType "xlColumnClustered"
#>
	param(
	[parameter(ValueFromPipeline = $true,Position=1)]
	[ValidateNotNullOrEmpty()]
	$InputObject,
	[parameter(Position=2)]
	[ValidateNotNullOrEmpty()]
	[string]$Path,
	[string]$WorksheetName = ("Sheet " + (Get-Date).Ticks),
	[string]$SheetPosition = "begin",
	[PSObject]$ChartType,
	[switch]$NoTypeInformation = $true,
	[switch]$AppendWorksheet = $true
	)

	begin{


		[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.Office.Interop.Excel")
		if($ChartType){
			[microsoft.Office.Interop.Excel.XlChartType]$ChartType = $ChartType
		}

		function Set-ClipBoard{
			param(
				[string]$text
			)
			process{
				Add-Type -AssemblyName System.Windows.Forms
				$tb = New-Object System.Windows.Forms.TextBox
				$tb.Multiline = $true
				$tb.Text = $text
				$tb.SelectAll()
				$tb.Copy()
			}
		}

		function Add-Array2Clipboard {
			param (
				[PSObject[]]$ConvertObject,
				[switch]$Header
			)
			process{
				$array = @()

				if ($Header) {
					$line =""
					$ConvertObject | Get-Member -MemberType Property,NoteProperty,CodeProperty | Select -Property Name | %{
						$line += ($_.Name.tostring() + "`t")
					}
					$array += ($line.TrimEnd("`t") + "`r")
				}
				else {
					foreach($row in $ConvertObject){
						$line =""
						$row | Get-Member -MemberType Property,NoteProperty | %{
							$Name = $_.Name
							if(!$Row.$Name -and $row.$name -ne 0 ){$Row.$Name = ""}
							$line += ([string]$Row.$Name + "`t")
						}
						$array += ($line.TrimEnd("`t") + "`r")
					}
				}
				Set-ClipBoard $array
			}
		}

		$excelApp = New-Object -ComObject "Excel.Application"
		$originalAlerts = $excelApp.DisplayAlerts
		$excelApp.DisplayAlerts = $false
		if(Test-Path -Path $Path -PathType "Leaf"){
			$workBook = $excelApp.Workbooks.Open($Path)
		}
		else{
			$workBook = $excelApp.Workbooks.Add()
		}
		$sheet = $excelApp.Worksheets.Add($workBook.Worksheets.Item(1))
		if(!$AppendWorksheet){
			$workBook.Sheets | where {$_ -ne $sheet} | %{$_.Delete()}
		}
		$sheet.Name = $WorksheetName
		if($SheetPosition -eq "end"){
			$nrSheets = $workBook.Sheets.Count
			2..($nrSheets) |%{
				$workbook.Sheets.Item($_).Move($workbook.Sheets.Item($_ - 1))
			}
		}
		$sheet.Activate()
		$array = @()
	}

	process{
		$array += $InputObject
	}

	end{
		Add-Array2Clipboard $array -Header:$True
		$selection = $sheet.Range("A1")
		$selection.Select() | Out-Null
		$sheet.Paste()
		$Sheet.UsedRange.HorizontalAlignment = [microsoft.Office.Interop.Excel.XlHAlign]::xlHAlignCenter
		Add-Array2Clipboard $array
		$selection = $sheet.Range("A2")
		$selection.Select() | Out-Null
		$sheet.Paste() | Out-Null
		$selection = $sheet.Range("A1")
		$selection.Select() | Out-Null

		$sheet.UsedRange.EntireColumn.AutoFit() | Out-Null
		$workbook.Sheets.Item(1).Select()
		if($ChartType){
			$sheet.Shapes.AddChart($ChartType) | Out-Null
		}
		$xlFixedFormat = [Microsoft.Office.Interop.Excel.XlFileFormat]::xlWorkbookDefault 
		
		
		[threading.thread]::CurrentThread.CurrentCulture = 'en-US'
		$workbook.SaveAs($path)
		$excelApp.DisplayAlerts = $originalAlerts
		$excelApp.Quit()
		Stop-Process -Name "Excel"
	}
}

#endregion functions

#getting the location of the script to create the log and instance directories.
#if error , need to stop the script

$scriptRoot = Split-Path -Parent $MyInvocation.MyCommand.Definition
$PSScriptRoot = $scriptRoot

Set-Location $PSScriptRoot

#using a cool powertip to check the paths
#http://powershell.com/cs/blogs/tips/archive/2015/09/18/using-hash-table-as-conditional-code-repository.aspx

try {
	$Creator = @{
		$True = { $null}
	  	$False = {$null = New-Item -Path $Path -ItemType Directory }
	}
	$Path = "$($PSScriptRoot)\Log" 
	& $Creator[(Test-Path $Path)] 
} catch {
	throw "Could not create the LOG directory"
}


#Path to the log
#XXX\LOG_MMddYY_hhmmss_indexlog.log
$OutPutLog = "$($path)\LOG_$(Get-Date -Format  'MMddyy_hhmmss')_IndexLog.log"

#starting the transcript
start-transcript -path $OutPutLog

#Trying to import SQLPS module. If could not, need to stop the script
try {
	import-module SQLPS -DisableNameChecking
} catch {
	throw "Could not import the SQLPS module"
}

#Check to SMO. If could not, need to stop the script
if ([Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") -eq $null -or ([System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")  -eq $null)) {
	Throw "SMO not avaliable"
}

#TSQL index fragmentation
$SQLIndex = "
SELECT object_name(IPS.object_id) AS [TableName], 
   SI.name AS [IndexName], 
   IPS.Index_type_desc, 
   IPS.avg_fragmentation_in_percent 
FROM sys.dm_db_index_physical_stats(db_id(), NULL, NULL, NULL , 'DETAILED') IPS
   JOIN sys.tables ST WITH (nolock) ON IPS.object_id = ST.object_id
   JOIN sys.indexes SI WITH (nolock) ON IPS.object_id = SI.object_id AND IPS.index_id = SI.index_id
WHERE ST.is_ms_shipped = 0 "#and IPS.page_count > 1000"


#variable to use to store the instance CSV created. Will use to generate the Excel files.
$PathCSV = @()

#Variable to load the instance names
$InputInstances = "$($PSScriptRoot)\InstanceNames.txt"

#Verbose preference to show evertything
$VerbosePreference = 'Continue'

Try {
	#Loading the Instance Names and storing in the InstanceName Variable by the awesome pipelinevariable common parameter
	Get-Content $InputInstances -PipelineVariable InstanceName -ErrorAction Stop  |
	#foreach in the instances
	ForEach-Object {
		Write-Verbose "Connecting instance $($InstanceName)" 
		try {
			try {
				#clean the InstanceSkipped variable. It is stored in the write error to skip the instances with error
				$InstanceSkipped.Clear()
				$Connection = new-object ("Microsoft.SqlServer.Management.Common.ServerConnection") $InstanceName  
				$Connection.Connect()
			} catch {
				Write-Error  "Could not connect on instance $($InstanceName) . Error : Error $($_.Exception.Message)" -ErrorVariable InstanceSkipped

			} finally {
				#if the instance was not skipped by any error
				if (!($InstanceSkipped)) {
					
					#replacing the "\\" by _ to create the paths and csv files
					$InstanceNamePath = $InstanceName -replace "\\","_"
					
					#acumulating the paths to the $pathCSV to use to generate the excel file and
					# storing the current path to the $path variable
					$PathCSV += $Path = "$($PSScriptRoot)\Instances\$($InstanceNamePath)" 
					
					#removing the path and all inside it if it exits. I am not handling error here or checking if exists do remove, I dont need it
					#if not exists and try to remove will generate error that will be supressed and if exists will be removed
					Remove-Item -Recurse -Path $Path -Force -ErrorAction SilentlyContinue 
					New-Item -Path $Path -ItemType Directory -Force 
					
					#connecting to the Instance. At this point I know that the instance is online
					#and storing the database enumeration to  the DatabaseName Variable using the awesome
					#pipelinevariable common parameter
					$SQLServer = new-object ("Microsoft.SqlServer.Management.Smo.Server")  $Connection 
					$SQLServer.Databases  |
					Where-Object {!($PSItem.IsSystemObject)} -PipelineVariable DatabaseName |
					foreach-object {
						#running the TSQL and saving to a CSV
						invoke-sqlcmd -ServerInstance $InstanceName -Query $SQLIndex -Database $DatabaseName.name -verbose:$false |
						Select-Object 	@{N='Table Name';E={$_.TableName}}, 
										@{N='Index Name';E={$_.IndexName}},
										@{N='Index Type';E={$_.Index_type_desc}},
										@{N='Fragmentation';E={$_.avg_fragmentation_in_percent}} |
						Export-Csv "$($Path)\$($DatabaseName.name).csv" -NoClobber -NoTypeInformation
					
					}  
				}	
			}
		} catch {
			Write-Error  "Oh Boy..something bad happened. Error : Error $($_.Exception.Message)" 
		}
	} 
} catch {
		Write-Error "Oh Boy..something bad happened. Error : Error $($_.Exception.Message)" 
}

Write-Verbose "Generating Excel...." 

try {

	if ($PathCSV) {

		$PathCSV |
		ForEach-Object {
			$PathToSave = "$($Psitem)\$(($Psitem -split '\\')[-1]).xlsx"
			dir "$($Psitem)\*.csv" | 
			ForEach-Object {
				try {
					$data = Import-Csv $Psitem.fullname
					Export-Xls $data $PathToSave -WorksheetName $Psitem.name -AppendWorksheet
					Write-Verbose "Excel created for $($PathToSave)"
				} catch {
					write-Error "Oh Boy..something bad happened. Error : Error $($_.Exception.Message)"
				}
				
			}	
		}
	}
} catch {
	write-Error "Oh Boy..something bad happened. Error : Error $($_.Exception.Message)"
}


Stop-Transcript