Database Index Status report in SQL Server via email alert

 DECLARE @command VARCHAR(5000),@cmd Varchar (1000),@profile_name Varchar(1000)  

DECLARE @xml NVARCHAR(MAX)  

DECLARE @body NVARCHAR(MAX)  


DECLARE @Index_details TABLE (

 Database_name sysname,

 Objectname sysname,

 Objectid Int,

 Indexid Int,

 partitionnum Int,

 frag decimal(30,2),

 Page_Count Int,

 Capture_Date Date)


Insert Into @Index_details

EXEC sp_MSForEachDB

'use [?]; SELECT DB_Name() As Database_name,Object_name (OBJECT_ID) As Object_name, [object_id] AS objectid ,index_id AS indexid,partition_number AS partitionnum,avg_fragmentation_in_percent AS frag, page_count,getdate()

FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, ''LIMITED'')

WHERE avg_fragmentation_in_percent > 10.0  -- Allow limited fragmentation

AND index_id > 0 -- Ignore heaps

AND page_count > 25 -- Ignore small tables

Order by avg_fragmentation_in_percent Desc,page_count Desc'


--Select * from @Index_details



SET @xml = CAST(( SELECT ID.Database_name As 'td',' ',ID.Objectname AS 'td' ,' ',ID.Objectid as 'td' ,' ',ID.Indexid 'td',' ',ID.partitionnum as 'td',  

' ',ID.frag as 'td' ,' ',ID.Page_Count as 'td',' ', convert(varchar(20), ID.Capture_Date,120) as 'td'  from @Index_details  ID  

FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))  


SET @body ='<html><body><H3>Database Index Status </H3>  

<table border = 1>   

<th> Database Name </th><th> Objectname </th> <th> Objectid </th> <th> Indexid </th> <th> Partition </th> <th> frag </th>  

<td bgcolor=#F0E68C><b>Pagecount<b> </td> <th>Capture_Date</th></tr>'      

--<th> Server Name </th><th> Database Name </th> <th> PhysicalFileName </th> <th> FileSizeMB </th> <th> SpaceUsedMB </th> <th> FreeSpaceMB </th><th> FreeSpace(%) </th> <th>Capture_Date</th></tr>'      

SET @body = @body + @xml +'</table></body></html>'  

Set @cmd= @@SERVERNAME + ': Database Index Status '  

SELECT Top 1 @profile_name=name  FROM msdb.dbo.sysmail_profile  


EXEC msdb.dbo.sp_send_dbmail  

@profile_name = @profile_name, -- replace with your SQL Database Mail Profile   

@body = @body,  

@body_format ='HTML',  

@recipients =  'IT.ESA-DBA@sqldbanow.com', -- replace with your email address  

@subject = @cmd

No comments:

Post a Comment