This object combines info from vwTable and vwIndexPhysicalStats.  

This view shows all of the information necessary for determining if you should REBUILD (formerly DBCC Reindex) or REORGANIZE (formerly DBCC IndexDefrag) your indexes.  

USE Admin

IF Object_ID('dbo.vwIndexPhysicalStatsTableSize') Is Not Null

      DROP VIEW dbo.vwIndexPhysicalStatsTableSize

go

create VIEW dbo.vwIndexPhysicalStatsTableSize

AS

/*    DATE        AUTHOR            REMARKS

      4/6/13            PPaiva            Initial creation.

     

      SELECT *

      FROM vwIndexPhysicalStatsTableSize

      WHERE DB = 'MyDB'

        AND PercFrag > 20

      SELECT *

      FROM vwIndexPhysicalStatsTableSize

      WHERE DB = 'MyDB'

      ORDER BY 1, 2, 3, 4, 5

      -- Cannot rebuild

      SELECT *

      FROM vwIndexPhysicalStatsTableSize

      WHERE DB = 'MyDB'

        AND (AllowPgLocks = 0 OR IsDisabled = 1)

     

*/

SELECT  s.Server,

            s.DB,

            s.SchemaName,

            s.ObjName,

            s.IndexName,

            s.PercFrag,

            s.TotalFrags,

            s.PagesPerFrag,

            dbo.udfFormatInteger(s.NumPages) NumPagesFMT,

            t.RowsFmt,

            t.DataMB,

            t.IndexMB IndexMBTotal,

            t.FileGroup,

            t.NumPartitions,

            t.NumCols,

           

            s.PartNum,

            s.IndexType,

            s.AllocDesc,

            s.IsUniq,

            s.IsPK,

            s.IsUniqCon,

            s.IndexDepth,

            s.IndexLevel,

            s.AllowPgLocks,

            s.IsDisabled,

            s.NumPages,

            t.Rows,

            s.object_id,

            s.database_id,

            s.ViewCreateDate

FROM vwIndexPhysicalStats s

LEFT JOIN vwTable t

      ON t.DB = s.DB

      AND t.object_id = s.object_id