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 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 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