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