Back to OIM Explorer

dbo.QBM_FTIndexFragmentation

Inline Table FunctionSQL_INLINE_TABLE_VALUED_FUNCTIONSandbox DB

Inline Table Function.

Source: sandbox-db sys.sql_modules

Source size: 1.124 characters

Interpretation

  • Database function. Usually supports views, validation, or calculated predicates; look at referenced-by entries for callers.

Relations

  • No extracted relations.

Typed Edges

  • No typed edges extracted for this source.

References

  • No direct source references extracted.

Referenced By

Complete Source

SQL31 lines
1CREATE FUNCTION dbo.QBM_FTIndexFragmentation(2  @TablePattern varchar(30),3  @PercentFragLimit int4) RETURNS TABLE5AS6RETURN(7SELECT8  DISTINCT9  LEFT(OBJECT_SCHEMA_NAME(frag.object_id), 30) AS SchemaName,10  LEFT(OBJECT_NAME(frag.object_id), 30) AS TableName,11  LEFT(six.name, 30) AS IndexName,12  frag.avg_fragmentation_in_percent AS PercentFrag,13  frag.index_id14FROM(15SELECT o.Object_id16FROM sys.objects o17WHERE18  o.type = 'U' AND o.name LIKE @TablePattern) AS t CROSS apply(19SELECT20  fr.object_id, fr.index_id, sum(convert(float, record_count) * avg_fragmentation_in_percent / 100.0) / sum(convert(float,21  record_count)) * 100.0 AS avg_fragmentation_in_percent22FROM sys.dm_db_index_physical_stats(DB_ID(), t.object_id, DEFAULT, 0, 'detailed') AS fr23WHERE24  fr.record_count > 025GROUP BY fr.object_id, fr.index_id) AS frag26JOIN sys.indexes six27  WITH(readpast)28  ON frag.object_id = six.object_id AND frag.index_id = six.index_id29WHERE30  six.name > ' ' AND frag.avg_fragmentation_in_percent >= isnull(@PercentFragLimit, 0) AND frag.object_ID = t.object_id AND six.is_hypothetical31= 0 AND six.type NOT IN(3, 4, 5, 6))
Open raw exported source
SQL ยท Raw8 lines
1create function dbo.QBM_FTIndexFragmentation(@TablePattern varchar(30) , @PercentFragLimit int  ) returns table  as return( select distinct  left(OBJECT_SCHEMA_NAME2(frag.object_id), 30) as SchemaName , left(OBJECT_NAME(frag.object_id), 30) as TableName , left(six.name, 30) as IndexName, frag.avg_fragmentation_in_percent3 as PercentFrag  , frag.index_id from ( select o.Object_id from sys.objects o where o.type = 'U' and o.name like @TablePattern ) as t cross apply ( select4 fr.object_id, fr.index_id,  sum(convert(float, record_count) * avg_fragmentation_in_percent / 100.0)/ sum(convert(float, record_count)) * 100.0 as avg_fragmentation_in_percent5 FROM sys.dm_db_index_physical_stats ( DB_ID(),  t.object_id,  DEFAULT,  0,  'detailed'  ) as fr where fr.record_count > 0 group by fr.object_id, fr.index_id6 ) as frag JOIN sys.indexes six with (readpast) ON frag.object_id = six.object_id AND frag.index_id = six.index_id where six.name > ' ' and frag.avg_fragmentation_in_percent7 >= isnull(@PercentFragLimit, 0)  and frag.object_ID = t.object_id  and six.is_hypothetical = 0  and six.type not in (3,4,5,6) ) 8