List SQL Indexes, which table they are on, and their fragmentation level

Vires_PtyLtd
Vires_PtyLtd used Ask the Experts™
on
I need to find a script (or scirpts) that

a. Lists the Indexes in an SQL Server, and which table they are indexing

b. List the fragmentation level of each Index

System is using Microsoft SQL Server 2008 R2

While I have found examples via websearch I havent found anything that matches exactly these requireemnts.

For instance "SELECT * FROM sys.indexes" lists a lot of data but it does not make much sense and does not tell you which table any given index is atached to.

Please Help.

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Chief Database Architect
Commented:
hi

1.
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE TABLE #tempwork1(
object_id nvarchar(MAX),
index_id nvarchar(MAX),
IndexName nvarchar(MAX))
--CURSOR TO FETCH DATA FROM dbo.sysdatabases
DECLARE @NAME NVARCHAR(MAX)
DECLARE @SQL NVARCHAR(MAX)
DECLARE Temp_Cursor CURSOR FOR
SELECT NAME FROM sys.databases --(un mark if you want a specific database)where database_id = 105
OPEN Temp_Cursor
-- Perform the first fetch.
FETCH NEXT FROM Temp_Cursor
INTO @NAME
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
-- This inserts information for all indexes in respective Databases
SELECT @SQL = 'USE ['+ @NAME +']
INSERT INTO #tempwork1 
SELECT object_id,
index_id,
name
FROM sys.indexes'
EXEC (@SQL)
-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM temp_cursor
INTO @NAME
END
CLOSE temp_cursor
DEALLOCATE temp_cursor
GO
--select * from #tempwork1
-- DROP TABLE #tempwork1
CREATE TABLE #tempwork2(
FullName nvarchar(MAX),
Index_Name nvarchar(MAX),
Index_Type nvarchar(MAX),
Row_Count nvarchar(MAX),
Percentage_Fregmentation nvarchar(MAX)) 
Insert into #tempwork2
SELECT DISTINCT QUOTENAME(DB_NAME(IPS.database_id)) 
+ N'.' 
+ QUOTENAME(OBJECT_SCHEMA_NAME(IPS.object_id, IPS.database_id)) 
+ N'.' 
+ QUOTENAME(OBJECT_NAME(IPS.object_id, IPS.database_id)) AS FullName,
SI.IndexName AS Index_Name,
IPS.Index_type_desc AS Index_Type,
IPS.record_count AS Row_Count,
STR(IPS.avg_fragmentation_in_percent,2,0) AS Percentage_Fregmentation
FROM sys.dm_db_index_physical_stats(NULL, NULL, NULL, NULL , 'DETAILED') AS IPS
JOIN #tempwork1 SI WITH (nolock) ON IPS.object_id = SI.object_id AND IPS.index_id = SI.index_id
WHERE (avg_fragmentation_in_percent > 70.0 AND IPS.index_id <> 0 AND IPS.database_id > 4)
ORDER BY FullName, Index_Name
GO
DROP TABLE #tempwork1
GO
DECLARE 
@Prof varchar(100), 
@Subj varchar(4000), 
@tableHTML NVARCHAR(MAX),
@Rec varchar(4000)
Set @Prof = 'MSG DBA'
Set @Subj = 'SQLServer Performance_Alert: Index_Fragmentation details ' + @@Servername
Set @Rec = 'YouEmail@Here.Com'
Set @tableHTML = 
N'<H4>SQLServer Performance_Alert: Index_Fragmentation_Percentage details </H4>' +
N'<Font size="8" face="Calibri">' +
N'<table border="1">' +
N'<tr> 
<th>FullName</th>
<th>Index_Name</th>
<th>Index_Type</th>
<th>Row_Count</th>
<th>Percentage_Fragmentation</th>' +
CAST ( ( SELECT td = A.FullName, '',
td = A.Index_Name, '',
td = A.Index_Type, '',
td = A.Row_Count, '',
td = A.Percentage_Fregmentation
FROM #tempwork2 as A
FOR XML PATH('tr'), TYPE ) AS NVARCHAR(MAX) ) +
N'</table>' +
N'</Font>';
Exec msdb.dbo.SP_Send_Dbmail
@Profile_Name = @Prof,
@Recipients = @Rec, 
@Subject = @Subj,
@Body = @tableHTML,
@body_format = 'HTML'
GO
DROP TABLE #tempwork2
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON

Open in new window

Author

Commented:
Thankyou this worked perfectly.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial