< Back
Get-DbaHelpIndex
Post
NAME Get-DbaHelpIndex
SYNOPSIS
Returns size, row and configuration information for indexes in databases.
SYNTAX
Get-DbaHelpIndex [[-SqlInstance] <Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter[]>] [[-SqlCredential]
<Pscredential>] [[-Database] <System.Object[]>] [[-ExcludeDatabase] <System.Object[]>] [[-InputObject]
<Microsoft.SqlServer.Management.Smo.Database[]>] [[-ObjectName] <String>] [-IncludeStats <Switch>]
[-IncludeDataTypes <Switch>] [-Raw <Switch>] [-IncludeFragmentation <Switch>] [-EnableException <Switch>]
[<CommonParameters>]
DESCRIPTION
This function will return detailed information on indexes (and optionally statistics) for all indexes in a
database, or a given index should one be passed along.
As this uses SQL Server DMVs to access the data it will only work in 2005 and up (sorry folks still running SQL
Server 2000).
For performance reasons certain statistics information will not be returned from SQL Server 2005 if an ObjectName
is not provided.
The data includes:
- ObjectName: the table containing the index
- IndexType: clustered/non-clustered/columnstore and whether the index is unique/primary key
- KeyColumns: the key columns of the index
- IncludeColumns: any include columns in the index
- FilterDefinition: any filter that may have been used in the index
- DataCompression: row/page/none depending upon whether or not compression has been used
- IndexReads: the number of reads of the index since last restart or index rebuild
- IndexUpdates: the number of writes to the index since last restart or index rebuild
- SizeKB: the size the index in KB
- IndexRows: the number of the rows in the index (note filtered indexes will have fewer rows than exist in the
table)
- IndexLookups: the number of lookups that have been performed (only applicable for the heap or clustered index)
- MostRecentlyUsed: when the index was most recently queried (default to 1900 for when never read)
- StatsSampleRows: the number of rows queried when the statistics were built/rebuilt (not included in SQL Server
2005 unless ObjectName is specified)
- StatsRowMods: the number of changes to the statistics since the last rebuild
- HistogramSteps: the number of steps in the statistics histogram (not included in SQL Server 2005 unless
ObjectName is specified)
- StatsLastUpdated: when the statistics were last rebuilt (not included in SQL Server 2005 unless ObjectName is
specified)
PARAMETERS
-Database [<System.Object[]>]
The database(s) to process. This list is auto-populated from the server. If unspecified, all databases will be
processed.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-EnableException [<Switch>]
By default, when something goes wrong we try to catch it, interpret it and give you a friendly warning message.
This avoids overwhelming you with "sea of red" exceptions, but is inconvenient because it basically disables
advanced scripting.
Using this switch turns this "nice by default" feature off and enables you to catch exceptions with your own
try/catch.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-ExcludeDatabase [<System.Object[]>]
The database(s) to exclude. This list is auto-populated from the server.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-IncludeDataTypes [<Switch>]
If this switch is enabled, the output will include the data type of each column that makes up a part of the
index definition (key and include columns).
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-IncludeFragmentation [<Switch>]
If this switch is enabled, the output will include fragmentation information.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-IncludeStats [<Switch>]
If this switch is enabled, statistics as well as indexes will be returned in the output (statistics
information such as the StatsRowMods will always be returned for indexes).
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-InputObject [<Microsoft.SqlServer.Management.Smo.Database[]>]
Allows piping from Get-DbaDatabase
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-ObjectName [<String>]
The name of a table for which you want to obtain the index information. If the two part naming convention for
an object is not used it will use the default schema for the executing user. If not passed it will return data
on all indexes in a given database.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Raw [<Switch>]
If this switch is enabled, results may be less user-readable but more suitable for processing by other code.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-SqlCredential [<Pscredential>]
Login to the target instance using alternative credentials. Accepts PowerShell credentials (Get-Credential).
Windows Authentication, SQL Server Authentication, Active Directory - Password, and Active Directory -
Integrated are all supported.
For MFA support, please use Connect-DbaInstance.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-SqlInstance [<Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter[]>]
The target SQL Server instance or instances.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
<CommonParameters>
This cmdlet supports the common parameters: Verbose, Debug,
ErrorAction, ErrorVariable, WarningAction, WarningVariable,
OutBuffer, PipelineVariable, and OutVariable. For more information, see
about_CommonParameters (https:/go.microsoft.com/fwlink/?LinkID=113216).
INPUTS
OUTPUTS
NOTES
Tags: Index
Author: Nic Cain, https://sirsql.net/
Website: https://dbatools.io
Copyright: (c) 2018 by dbatools, licensed under MIT
License: MIT https://opensource.org/licenses/MIT
-------------------------- EXAMPLE 1 --------------------------
PS C:\\>Get-DbaHelpIndex -SqlInstance localhost -Database MyDB
Returns information on all indexes on the MyDB database on the localhost.
-------------------------- EXAMPLE 2 --------------------------
PS C:\\>Get-DbaHelpIndex -SqlInstance localhost -Database MyDB,MyDB2
Returns information on all indexes on the MyDB & MyDB2 databases.
-------------------------- EXAMPLE 3 --------------------------
PS C:\\>Get-DbaHelpIndex -SqlInstance localhost -Database MyDB -ObjectName dbo.Table1
Returns index information on the object dbo.Table1 in the database MyDB.
-------------------------- EXAMPLE 4 --------------------------
PS C:\\>Get-DbaHelpIndex -SqlInstance localhost -Database MyDB -ObjectName dbo.Table1 -IncludeStats
Returns information on the indexes and statistics for the table dbo.Table1 in the MyDB database.
-------------------------- EXAMPLE 5 --------------------------
PS C:\\>Get-DbaHelpIndex -SqlInstance localhost -Database MyDB -ObjectName dbo.Table1 -IncludeDataTypes
Returns the index information for the table dbo.Table1 in the MyDB database, and includes the data types for the
key and include columns.
-------------------------- EXAMPLE 6 --------------------------
PS C:\\>Get-DbaHelpIndex -SqlInstance localhost -Database MyDB -ObjectName dbo.Table1 -Raw
Returns the index information for the table dbo.Table1 in the MyDB database, and returns the numerical data
without localized separators.
-------------------------- EXAMPLE 7 --------------------------
PS C:\\>Get-DbaHelpIndex -SqlInstance localhost -Database MyDB -IncludeStats -Raw
Returns the index information for all indexes in the MyDB database as well as their statistics, and formats the
numerical data without localized separators.
-------------------------- EXAMPLE 8 --------------------------
PS C:\\>Get-DbaHelpIndex -SqlInstance localhost -Database MyDB -IncludeFragmentation
Returns the index information for all indexes in the MyDB database as well as their fragmentation
-------------------------- EXAMPLE 9 --------------------------
PS C:\\>Get-DbaDatabase -SqlInstance sql2017 -Database MyDB | Get-DbaHelpIndex
Returns the index information for all indexes in the MyDB database
RELATED LINKS
https://dbatools.io/Get-DbaHelpIndex
SYNOPSIS
Returns size, row and configuration information for indexes in databases.
SYNTAX
Get-DbaHelpIndex [[-SqlInstance] <Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter[]>] [[-SqlCredential]
<Pscredential>] [[-Database] <System.Object[]>] [[-ExcludeDatabase] <System.Object[]>] [[-InputObject]
<Microsoft.SqlServer.Management.Smo.Database[]>] [[-ObjectName] <String>] [-IncludeStats <Switch>]
[-IncludeDataTypes <Switch>] [-Raw <Switch>] [-IncludeFragmentation <Switch>] [-EnableException <Switch>]
[<CommonParameters>]
DESCRIPTION
This function will return detailed information on indexes (and optionally statistics) for all indexes in a
database, or a given index should one be passed along.
As this uses SQL Server DMVs to access the data it will only work in 2005 and up (sorry folks still running SQL
Server 2000).
For performance reasons certain statistics information will not be returned from SQL Server 2005 if an ObjectName
is not provided.
The data includes:
- ObjectName: the table containing the index
- IndexType: clustered/non-clustered/columnstore and whether the index is unique/primary key
- KeyColumns: the key columns of the index
- IncludeColumns: any include columns in the index
- FilterDefinition: any filter that may have been used in the index
- DataCompression: row/page/none depending upon whether or not compression has been used
- IndexReads: the number of reads of the index since last restart or index rebuild
- IndexUpdates: the number of writes to the index since last restart or index rebuild
- SizeKB: the size the index in KB
- IndexRows: the number of the rows in the index (note filtered indexes will have fewer rows than exist in the
table)
- IndexLookups: the number of lookups that have been performed (only applicable for the heap or clustered index)
- MostRecentlyUsed: when the index was most recently queried (default to 1900 for when never read)
- StatsSampleRows: the number of rows queried when the statistics were built/rebuilt (not included in SQL Server
2005 unless ObjectName is specified)
- StatsRowMods: the number of changes to the statistics since the last rebuild
- HistogramSteps: the number of steps in the statistics histogram (not included in SQL Server 2005 unless
ObjectName is specified)
- StatsLastUpdated: when the statistics were last rebuilt (not included in SQL Server 2005 unless ObjectName is
specified)
PARAMETERS
-Database [<System.Object[]>]
The database(s) to process. This list is auto-populated from the server. If unspecified, all databases will be
processed.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-EnableException [<Switch>]
By default, when something goes wrong we try to catch it, interpret it and give you a friendly warning message.
This avoids overwhelming you with "sea of red" exceptions, but is inconvenient because it basically disables
advanced scripting.
Using this switch turns this "nice by default" feature off and enables you to catch exceptions with your own
try/catch.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-ExcludeDatabase [<System.Object[]>]
The database(s) to exclude. This list is auto-populated from the server.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-IncludeDataTypes [<Switch>]
If this switch is enabled, the output will include the data type of each column that makes up a part of the
index definition (key and include columns).
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-IncludeFragmentation [<Switch>]
If this switch is enabled, the output will include fragmentation information.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-IncludeStats [<Switch>]
If this switch is enabled, statistics as well as indexes will be returned in the output (statistics
information such as the StatsRowMods will always be returned for indexes).
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-InputObject [<Microsoft.SqlServer.Management.Smo.Database[]>]
Allows piping from Get-DbaDatabase
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-ObjectName [<String>]
The name of a table for which you want to obtain the index information. If the two part naming convention for
an object is not used it will use the default schema for the executing user. If not passed it will return data
on all indexes in a given database.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Raw [<Switch>]
If this switch is enabled, results may be less user-readable but more suitable for processing by other code.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-SqlCredential [<Pscredential>]
Login to the target instance using alternative credentials. Accepts PowerShell credentials (Get-Credential).
Windows Authentication, SQL Server Authentication, Active Directory - Password, and Active Directory -
Integrated are all supported.
For MFA support, please use Connect-DbaInstance.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-SqlInstance [<Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter[]>]
The target SQL Server instance or instances.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
<CommonParameters>
This cmdlet supports the common parameters: Verbose, Debug,
ErrorAction, ErrorVariable, WarningAction, WarningVariable,
OutBuffer, PipelineVariable, and OutVariable. For more information, see
about_CommonParameters (https:/go.microsoft.com/fwlink/?LinkID=113216).
INPUTS
OUTPUTS
NOTES
Tags: Index
Author: Nic Cain, https://sirsql.net/
Website: https://dbatools.io
Copyright: (c) 2018 by dbatools, licensed under MIT
License: MIT https://opensource.org/licenses/MIT
-------------------------- EXAMPLE 1 --------------------------
PS C:\\>Get-DbaHelpIndex -SqlInstance localhost -Database MyDB
Returns information on all indexes on the MyDB database on the localhost.
-------------------------- EXAMPLE 2 --------------------------
PS C:\\>Get-DbaHelpIndex -SqlInstance localhost -Database MyDB,MyDB2
Returns information on all indexes on the MyDB & MyDB2 databases.
-------------------------- EXAMPLE 3 --------------------------
PS C:\\>Get-DbaHelpIndex -SqlInstance localhost -Database MyDB -ObjectName dbo.Table1
Returns index information on the object dbo.Table1 in the database MyDB.
-------------------------- EXAMPLE 4 --------------------------
PS C:\\>Get-DbaHelpIndex -SqlInstance localhost -Database MyDB -ObjectName dbo.Table1 -IncludeStats
Returns information on the indexes and statistics for the table dbo.Table1 in the MyDB database.
-------------------------- EXAMPLE 5 --------------------------
PS C:\\>Get-DbaHelpIndex -SqlInstance localhost -Database MyDB -ObjectName dbo.Table1 -IncludeDataTypes
Returns the index information for the table dbo.Table1 in the MyDB database, and includes the data types for the
key and include columns.
-------------------------- EXAMPLE 6 --------------------------
PS C:\\>Get-DbaHelpIndex -SqlInstance localhost -Database MyDB -ObjectName dbo.Table1 -Raw
Returns the index information for the table dbo.Table1 in the MyDB database, and returns the numerical data
without localized separators.
-------------------------- EXAMPLE 7 --------------------------
PS C:\\>Get-DbaHelpIndex -SqlInstance localhost -Database MyDB -IncludeStats -Raw
Returns the index information for all indexes in the MyDB database as well as their statistics, and formats the
numerical data without localized separators.
-------------------------- EXAMPLE 8 --------------------------
PS C:\\>Get-DbaHelpIndex -SqlInstance localhost -Database MyDB -IncludeFragmentation
Returns the index information for all indexes in the MyDB database as well as their fragmentation
-------------------------- EXAMPLE 9 --------------------------
PS C:\\>Get-DbaDatabase -SqlInstance sql2017 -Database MyDB | Get-DbaHelpIndex
Returns the index information for all indexes in the MyDB database
RELATED LINKS
https://dbatools.io/Get-DbaHelpIndex