The SQL Server Database Engine automatically maintains indexes whenever insert, update, or delete operations are made to the underlying data. Over time these modifications can cause the information in the index to become scattered in the database (fragmented). Fragmentation exists when indexes have pages in which the logical ordering, based on the key value, does not match the physical ordering inside the data file. Heavily fragmented indexes can degrade query performance and cause your application to respond slowly.


Issues which I faced in my organization is my company database contains more than 700 tables identifying fragmentation percentage of all table is time consuming process as for each table we need to used SYS. DM_DB_INDEX_PHYSICAL_STATS to avoid this generally written the below syntax to list all table Fragmentation percentage, This syntax can also be used to generate report periodically to identify the fragmentation percentage.


Once fragmentation percentage is identified then we need to plan for de-fragmentation according to the fragmentation level


 

-- be safe for sys.indexes although BOL says "sys.dm_db_index_physical_stats requires only an Intent-Shared (IS)"
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
  
-- if object_id('tempdb.dbo.#IDXFRAG') is not null drop TABLE #IDXFRAG
if object_id('tempdb.dbo.#IDXFRAG') is not null
DROP table tempdb.dbo.#IDXFRAG
  
    CREATE TABLE #IDXFRAG
    (   DbName                          sysname,
        ObjName                         sysname,
        IdxName                         sysname     NULL,
    -- columns below are exactly as generated by SYS.DM_DB_INDEX_PHYSICAL_STATS function
        database_id                     smallint,
        [object_id]                     int,
        index_id                        int,
        partition_number                int,
        index_type_desc                 nvarchar(60) COLLATE Latin1_General_CI_AI NULL,
        alloc_unit_type_desc            nvarchar(60) COLLATE Latin1_General_CI_AI NULL,
        index_depth                     tinyint NULL,
        index_level                     tinyint NULL,
        avg_fragmentation_in_percent    float NULL,
        fragment_count                  bigint NULL,
        avg_fragment_size_in_pages      float NULL,
        page_count                      bigint NULL,
        avg_page_space_used_in_percent  float NULL,
        record_count                    bigint NULL,
        ghost_record_count              bigint NULL,
        version_ghost_record_count      bigint NULL,
        min_record_size_in_bytes        int NULL,
        max_record_size_in_bytes        int NULL,
        avg_record_size_in_bytes        float NULL,
        forwarded_record_count          bigint NULL
   -- ,   primary key nonclustered (DbName, ObjName,[object_id],index_id,partition_number)   -- IdxName is NULL if HEAP (index_id=0)
    )
GO
  
IF NOT EXISTS (SELECT * FROM tempdb.sys.indexes WHERE object_id = OBJECT_ID(N'tempdb..#IDXFRAG') AND name = N'IDXFRAG_CI')
    create index IDXFRAG_CI on #IDXFRAG (DbName,ObjName,IdxName)
  
-- tip: you can re-run in SSMS for different db's by 
declare tblcur cursor for
    select  object_id, TblName=schema_name(schema_id)+'.'+name
    from    sys.tables where type = 'U'
    order by TblName 
  
declare @dbid smallint, @objid int, @TblName nvarchar(257)
select @dbid=db_id()    --, @objid=OBJECT_ID('SEDOL_PRICE')
--select @dbid, @objid
open tblcur
fetch next from tblcur into @objid, @TblName
while @@fetch_status=0
 begin
 -- print @TblName        -- DEBUG only
    delete from #IDXFRAG where DbName=db_name() and [object_id]=@objid      -- allow re-runs and multiple db's
    insert into #IDXFRAG
        select  DbName=db_name(), ObjName=@TblName, IdxName=I.name,
                database_id,s.[object_id],s.index_id, partition_number,
                index_type_desc,alloc_unit_type_desc,
                index_depth,index_level,
                avg_fragmentation_in_percent,fragment_count,
                avg_fragment_size_in_pages,page_count,
                avg_page_space_used_in_percent,record_count,
                ghost_record_count, version_ghost_record_count,
                min_record_size_in_bytes,max_record_size_in_bytes,
                avg_record_size_in_bytes,forwarded_record_count 
        from    SYS.DM_DB_INDEX_PHYSICAL_STATS(@dbid,@objid,NULL,NULL,'SAMPLED')    S
        join    sys.indexes                                                         I on I.object_id= @objid and I.index_id=S.index_id 
        --where   S.avg_fragmentation_in_percent > 20 AND S.page_count > 8 
            
    fetch next from tblcur into @objid, @TblName
 end
close tblcur
deallocate tblcur
go
  
select  DbName, ObjName,IdxName, index_id, partition_number,index_type_desc, index_depth, index_level, avg_fragmentation_in_percent, fragment_count, avg_fragment_size_in_pages, page_count, avg_page_space_used_in_percent, record_count, forwarded_record_count    --, ghost_record_count, version_ghost_record_count, min_record_size_in_bytes, max_record_size_in_bytes, avg_record_size_in_bytes
from    #IDXFRAG
order by DbName,ObjName,IdxName    -- IDXFRAG_CI
go

See below gallery for more
http://gallery.technet.microsoft.com/List-of-all-tables-with-b1586515#content