SQL : View Table Index Data

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