Below is a script that will run against every table in every database on your Sql Server instance and output, among other things, the average index fragmentation percentage.
It can be a bit heavy when running against large databases with thousands of tables so I would recommend running it during downtime or outside office hours.
This script returns fragmentation stats by utilizing a DMV.
When Nulls are presented as parameters this DMV works at a server level and uses preset defaults.
[sys].[dm_db_index_physical_stats](NULL, NULL, NULL, NULL, NULL)
However it also accepts the parameter options below to limited the results returned.
[sys].[dm_db_index_physical_stats](Database_Id, Object_Id, Index_Id, partition_number, MODE)
MODE OPTIONS:
Mode specifies the scan level that is used to obtain statistics. Valid inputs are DEFAULT, NULL, LIMITED, SAMPLED, or DETAILED. The default (NULL) is LIMITED.
DEFAULT will have 12 columns full of data and then NULLS in the remainder.
SAMPLED will have 21 columns full of data.
LIMITED will have 12 columns of data and the NULLS in the remainder.
DETAILED will have 21 columns full of data.
Note: I’ve capture more fields in the temp table #Index than I use in the final select statement. This is because you might want to include additional index related fields i.e. check if the index is unique etc. If all you want is the index name in the final select statement you can tidy up the query as needed and exclude the unwanted additional fields.
SET NOCOUNT ON IF OBJECT_ID('tempdb..#Schema') IS NOT NULL DROP TABLE #Schema IF OBJECT_ID('tempdb..#Index') IS NOT NULL DROP TABLE #Index DECLARE @Sql AS VARCHAR(MAX) DECLARE @Database TABLE ( DbId INT ,DbName SYSNAME ) DECLARE @DbName AS SYSNAME DECLARE @DbId AS INT CREATE TABLE #Schema ( DbId INT ,DbName SYSNAME ,Object_Id INT NULL ,ObjectName SYSNAME NULL ,TableFlag BIT ,ViewFlag BIT ,Schema_Id INT NULL ,SchemaName SYSNAME NULL ) CREATE TABLE #Index ( DbId INT ,DbName SYSNAME ,Object_Id INT NOT NULL ,NAME SYSNAME NULL ,index_id INT NOT NULL ,type TINYINT NOT NULL ,type_desc NVARCHAR(60) NULL ,is_unique BIT NULL ,data_space_id INT NULL ,ignore_dup_key BIT NULL ,is_primary_key BIT NULL ,is_unique_constraint BIT NULL ,fill_factor TINYINT NOT NULL ,is_padded BIT NULL ,is_disabled BIT NULL ,is_hypothetical BIT NULL ,allow_row_locks BIT NULL ,allow_page_locks BIT NULL ,has_filter BIT NULL ,filter_definition NVARCHAR(max) NULL ) INSERT INTO @Database ( DbId ,DbName ) SELECT dbid ,NAME FROM master.dbo.sysdatabases WHERE NAME <> 'tempdb' ORDER BY NAME ASC SET @DbId = 0 WHILE @DbId IS NOT NULL BEGIN SET @DbId = ( SELECT MIN(DbId) FROM @Database WHERE DbId > @DbId ) SET @DbName = ( SELECT DbName FROM @Database WHERE DbId = @DbId ) SET @Sql = ' INSERT INTO #Schema ( DbId ,DbName ,Object_Id ,ObjectName ,TableFlag ,ViewFlag ,Schema_Id ,SchemaName ) SELECT ' + CONVERT(VARCHAR(128), @DbId) + ' AS DbId ,' + '''' + @DbName + '''' + ' AS DbName ,o.Object_Id ,o.NAME AS ObjectName ,CASE WHEN o.type = ''IT'' THEN 1 WHEN o.type = ''S'' THEN 1 WHEN o.type = ''TT'' THEN 1 WHEN o.type = ''U'' THEN 1 ELSE NULL END AS TableFlag ,CASE WHEN o.type = ''V'' THEN 1 ELSE NULL END AS ViewFlag ,s.Schema_Id ,s.NAME AS SchemaName FROM ' + QUOTENAME(@DbName) + '.sys.objects AS o WITH (NOLOCK) INNER JOIN ' + QUOTENAME(@DbName) + '.sys.schemas AS s WITH (NOLOCK) ON o.schema_id = s.schema_id ' EXEC (@Sql) SET @Sql = ' INSERT INTO #Index ( DbId ,DbName ,object_id ,NAME ,index_id ,type ,type_desc ,is_unique ,data_space_id ,ignore_dup_key ,is_primary_key ,is_unique_constraint ,fill_factor ,is_padded ,is_disabled ,is_hypothetical ,allow_row_locks ,allow_page_locks ,has_filter ,filter_definition ) SELECT ' + CONVERT(VARCHAR(128), @DbId) + ' AS DbId ,' + '''' + @DbName + '''' + ' AS DbName ,object_id ,NAME ,index_id ,type ,type_desc ,is_unique ,data_space_id ,ignore_dup_key ,is_primary_key ,is_unique_constraint ,fill_factor ,is_padded ,is_disabled ,is_hypothetical ,allow_row_locks ,allow_page_locks ,has_filter ,filter_definition FROM ' + QUOTENAME(@DbName) + '.sys.indexes WITH (NOLOCK) ' EXEC (@Sql) END SELECT ROW_NUMBER() OVER ( ORDER BY s.DbName ASC ,s.SchemaName ASC ,s.ObjectName ) AS Row ,s.DbName ,s.SchemaName AS SchemaName ,s.Object_Id AS ObjectId ,s.ObjectName ,s.TableFlag ,s.ViewFlag ,i.Name AS IndexName ,ddips.index_type_desc AS IndexType ,ddips.page_count AS Pages ,ddips.record_count AS Records ,ddips.fragment_count AS Frags ,ddips.avg_fragmentation_in_percent AS AvgFragPercent FROM sys.dm_db_index_physical_stats(NULL, NULL, NULL, NULL, 'SAMPLED') AS ddips INNER JOIN #Schema AS s ON ddips.database_id = s.DbId INNER JOIN #Index AS i ON s.DbId = i.DbId AND s.Object_Id = i.Object_Id AND ddips.index_id = i.index_id AND ddips.object_id = s.Object_Id WHERE ( s.TableFlag = 1 OR s.ViewFlag = 1 ) ORDER BY s.DbName ASC ,s.SchemaName ASC ,s.ObjectName
Tidbytez 