Functions/Get-SqlIndexFragmentation.ps1

function Get-SqlIndexFragmentation {
    <#
.SYNOPSIS
    Get SQL Index Fragmentation
.DESCRIPTION
    Get SQL Index Fragmentation, on a database or a table LIKE string
.PARAMETER ServerInstance
    Database instance that you wish to connect to. Defaults to $env:COMPUTERNAME
.PARAMETER Database
    String containing text for database name to be LIKE. Wildcards can be used.
.PARAMETER Table
    String containing text for table name to be LIKE. Wildcards can be used.
.EXAMPLE
    Get-SqlIndexFragmentation
 
    Returns index fragmentation on the default database instance on the local computer
.NOTES
    1 - User running this function must have Windows authentication to the database server
    2 - tempdb is excluded from the list of tables
    3 - outputs a psobject containing:
        DatabaseName, Schema, Table, Index, Avg_Fragmentation_In_Percent, Page_Count
#>


    [CmdletBinding()]
    [outputtype('psobject')]
    param (
        [string] $ServerInstance = $env:COMPUTERNAME,

        [string] $Database = '*',

        [string] $Table = '*',

        [switch] $IncludeSystemDatabase,

        [ValidateRange(10, 99)]
        [int] $MinFragmentation = 10,

        [int] $MinPageCount = 200
    )

    begin {
        Write-Verbose -Message "Starting [$($MyInvocation.Mycommand)]"
        Write-Verbose -Message "ServerInstance [$ServerInstance]"
        Write-Verbose -Message "Database [$Database]"
        Write-Verbose -Message "Table [$Table]"
        Write-Verbose -Message "IncludeSystemDatabase [$IncludeSystemDatabase]"
        Write-Verbose -Message "MinFragmentation [$MinFragmentation]"
        Write-Verbose -Message "MinPageCount [$MinPageCount]"
        try {
            $DatabaseList = Get-SqlDatabase -ServerInstance $ServerInstance -Database $Database -IncludeSystemDatabase:$IncludeSystemDatabase
            if (-not $DatabaseList) {
                Write-Error "No databases that match [$Database]"
                return
            }
        } catch {
            Write-Error "Could not make SQL connection to [$ServerInstance], either server not up, or no permissions to connect."
            return
        }
        $FragQuery = "
            SELECT
                db_name() AS 'DBName',
                S.name AS 'Schema',
                T.name AS 'Table',
                I.name AS 'Index',
                DDIPS.avg_fragmentation_in_percent,
                DDIPS.page_count
            FROM
                sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS DDIPS
            INNER JOIN
                sys.tables T on T.object_id = DDIPS.object_id
            INNER JOIN
                sys.schemas S on T.schema_id = S.schema_id
            INNER JOIN
                sys.indexes I ON I.object_id = DDIPS.object_id
                AND DDIPS.index_id = I.index_id
            WHERE
                DDIPS.database_id = DB_ID()
            AND
                DDIPS.avg_fragmentation_in_percent >= $MinFragmentation
            AND
                DDIPS.page_count >= $MinPageCount
            AND
                I.name IS NOT NULL
            ORDER BY
                'DBName', 'Schema', 'Table', 'Index'
        "

    }

    process {
        $DatabaseList | ForEach-Object -Begin { $FragResult = @() } -Process {
            $CurDatabase = $_
            $FragResult += Invoke-Sqlcmd -ServerInstance $ServerInstance -Database $CurDatabase.Name -Query $FragQuery -QueryTime 900
        }
    }

    end {
        $FragResult = $FragResult | Select-Object DbName, Schema, Table, Index, @{Name = 'Avg_Fragmentation_In_Percent'; Expr = { ([float] ('{0:f2}' -f $_.avg_fragmentation_in_percent)) } }, Page_Count
        $FragResult = $FragResult | Sort-Object DbName, Schema, Table, Index
        Write-Output -InputObject $FragResult | Where-Object { $_.Table -like $Table }
        Write-Verbose -Message "Ending [$($MyInvocation.Mycommand)]"
    }
}