SQL : View Table Index Data

SQL : View Table Indexes

The following SQL will show index related information for a specific table within the local database context, this data will contain the index name and indexed fields:

EXEC sp_helpindex ‘messages’

You can view index fragmentation data on a per table basis using the command:

DBCC SHOWCONTIG tablename

For a more easy-to-read fragmentation report using the following query which will display indexes fragmented more than 40%, if you wish to modify the display criteria change the line highlighted in red (WHERE LogicalFrag > 40):

SET NOCOUNT ON

print ‘Cleaning up old temp tables’
IF object_id(‘tempdb..#spt_space’) IS NOT NULL
    BEGIN
       DROP TABLE #spt_space
    END

IF object_id(‘tempdb..#bigtables’) IS NOT NULL
    BEGIN
       DROP TABLE #bigtables
    END

IF object_id(‘tempdb..#fraglist’) IS NOT NULL
    BEGIN
       DROP TABLE #fraglist
    END
    
declare @tablename varchar (128)
declare @id int
declare @Cnt smallint
declare @nrc_mask int
declare @dbname varchar (200)

CREATE TABLE #spt_space
    (    aidi    int,
        [rows]        int null,
        reserved    dec(15) null,
        AUTOSTATS      char(2) null,
        [Last Updated] datetime
    )
    
CREATE TABLE #fraglist (
   ObjectName CHAR (255), ObjectId INT,
   IndexName CHAR (255), IndexId INT,
   Lvl INT, CountPages INT,
   CountRows INT, MinRecSize INT,
   MaxRecSize INT, AvgRecSize INT,
   ForRecCount INT, Extents INT,
   ExtentSwitches INT, AvgFreeBytes INT,
   AvgPageDensity INT, ScanDensity DECIMAL,
   BestCount INT, ActualCount INT,
   LogicalFrag DECIMAL, ExtentFrag DECIMAL
    )
    
CREATE TABLE #bigtables (
    i int NOT NULL IDENTITY(1,1),
    tablename varchar(128)
    )

/* Create cursor for per-table analysis and analyse tables for INDEX and AUTOSTATS data */
print ‘Creating cursor or per-table analysis’
declare tables cursor for
   select TABLE_SCHEMA + ‘.’ + TABLE_NAME
   from INFORMATION_SCHEMA.TABLES
   where TABLE_TYPE = ‘BASE TABLE’

print ‘Executing tables cursor’
open tables
    /*
    ** Set NORECOMPUTE mask
    */
    set @nrc_mask = 16777216

fetch next
   from tables
   into @tablename
while @@fetch_status = 0
begin

    select @id = object_id(@tablename)

    insert into #spt_space (aidi, reserved)
        select @id, sum(reserved)
            from sysindexes
                where indid in (0, 1, 255)
                    and id = @id
    update #spt_space set     [rows] = i.[rows]
            from #spt_space inner join sysindexes i
            on #spt_space.aidi = i.id
                where i.indid < 2
                    and i.id = @id
    update #spt_space set     [AUTOSTATS] =
                case (si.status & @nrc_mask)
                when @nrc_mask then ‘OFF’
                else ‘ON’
            end,
               [Last Updated] = stats_date(#spt_space.aidi, si.indid)
        from sysindexes si inner join #spt_space
            on #spt_space.aidi = si.id
        where si.id = @id            AND        — Table
              si.indid BETWEEN 1 AND 254            — Skip HEAP/TEXT index
   fetch next
      from tables
      into @tablename
end

print ‘Closing and deallocating tables cursor’
close tables
deallocate tables
    
INSERT #bigtables(tablename)
SELECT object_name(aidi) as tablename
FROM #spt_space
WHERE rows >= 100000

SELECT @Cnt = count(*) FROM #bigtables

DECLARE @DTime datetime
Set @DTime = GETDATE()

/* Obtain INDEX fragmentation data */
print ‘Updating Fragmentation temp table’
SET @id = 1
WHILE @id <= @Cnt
BEGIN
   SELECT @tablename = tablename FROM #bigtables WHERE i = @id
   INSERT INTO #fraglist
   EXEC (‘DBCC SHOWCONTIG (”’ + @tablename + ”’)
    WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS’)     
   SET @id = @id + 1
END

/* Display Fragmentation Data > 40% */
SELECT ObjectName, ObjectId, IndexName, IndexId, Lvl, CountPages,
           ExtentSwitches, ScanDensity, BestCount , ActualCount ,LogicalFrag  
FROM #fraglist
WHERE LogicalFrag > 40
ORDER BY LogicalFrag DESC
       
DROP TABLE #spt_space
DROP Table #bigtables
DROP TABLE #fraglist