how to know index fragmentation?

 pre requisites:-

create table fragmentation (schemaname varchar(250), tablename varchar(50),indexname varchar(50),fragmentation float)


syntax is here:-




declare @schemaname varchar(250),
@tablename varchar(250),
@indexname varchar(250),
@fragmentation float
begin
declare fragmentation cursor local for select schema_name(cast(objectpropertyex(a.object_id,'schema_id')as int)),object_name(a.object_id),a.name, b.avg_fragmentation_in_percent FROM sys.indexes a join sys.dm_db_index_physical_stats(db_id(),null,null,null,null) b on a.object_id=b.object_id and a.index_id=b.index_id where avg_fragmentation_in_percent>30
open fragmentation
fetch next from fragmentation into @schemaname,@tablename,@indexname,@fragmentation
while @@FETCH_STATUS=0
begin

insert into fragmentation values (@schemaname,@tablename,@indexname,@fragmentation)
fetch next from fragmentation into @schemaname,@tablename,@indexname,@fragmentation

end

end
close fragmentation
deallocate fragmentation
select * from fragmentation