LOG OVERSIZE Report for all DB

/******************************************************************************************** 
**Name: p_log_oversize.
**Desc: log oversize report - if log_file bigger then data_file. 
** 
**Called by: 
**            execute p_log_oversize 
**              
**Input:          Output: E-Mail
**      -----------------------------------------------------
**Author : Mushkatin Vadim.Israel. Vadimm@bezeq.com 
**Created: 30/7/2002. 
********************************************************************************************/set quoted_identifier off
set nocount on
go

Create  proc  p_log_oversize as

declare    @subject    varchar(50),
           @count      int     

set @subject = @@servername + ' Log Oversize '

if object_id('bzmaint..file_data') is not null
   drop table bzmaint..file_data
if object_id('bzmaint..file_log')  is not null
   drop table bzmaint..file_log

select fileid,size,f.dbid,d.name [db_name], f.name [file_name] 
into bzmaint..file_data
from master..sysaltfiles f,master..sysdatabases d
where f.dbid = d.dbid and fileid = 1

select fileid,size,f.dbid,d.name [db_name], f.name [file_name]
into bzmaint..file_log
from master..sysaltfiles f,master..sysdatabases d
where f.dbid = d.dbid and fileid = 2

select @count = count(*) 
from bzmaint..file_data d, bzmaint..file_log l
where l.dbid = d.dbid and l.size > d.size  

if @count > 0
begin  
   SET ANSI_NULLS    ON
   SET ANSI_WARNINGS  ON
   Exec master..xp_sendmail @recipients ='Your@E-MailAddress',
      @query = 'select substring(l.[db_name],1,20) as dbname , l.size log_size ,
                       d.size data_size,substring(l.[file_name],1,20) as log_file_name 
                from bzmaint..file_data d, bzmaint..file_log l
                where l.dbid = d.dbid and l.size > d.size ', 
      @subject = @subject ,
      @message = 'The output of p_log_oversize:',
      @attach_results = 'TRUE', @width = 250
End

Go