-- Stored Procedure: dbo.ArchUtilDailyChangeReport
-- Bill Wunder

use admin
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ArchUtilDailyChangeReport]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ArchUtilDailyChangeReport]
GO




CREATE PROCEDURE [dbo].[ArchUtilDailyChangeReport] 
 @Recipients varchar(255) 
AS
/*******************************************************************************************************
* admin.dbo.ArchUtilDailyChangeReport 
* Creator:       bw
* Date:          11-10-2003
*
* Description:   email the DDL changes logged in the last 12 hours
* Notes:         Should be executed immediately after the archive operation
*
* Usage:
      EXECUTE admin.dbo.ArchUtilDailyChangeReport 'bill.wunder@wallst.com'
*
*
* Modifications:   
*          Developer Name     Date     Brief Description
*          ------------------ -------- ------------------------------------------------------------
*                    
********************************************************************************************************/

---------------------------------------------
-- declare variables
---------------------------------------------
declare @Subject varchar(100)
 , @Query varchar(1024)
 , @Date varchar(20)


---------------------------------------------
-- create temp tables
---------------------------------------------

---------------------------------------------
-- set session variables
---------------------------------------------
SET NOCOUNT ON


---------------------------------------------
-- body of stored procedure
---------------------------------------------

set @Date = dateadd(hour,-18, getdate())

set @Subject = 'Daily Database changes on Server ' + @@servername
set @Query = 'set nocount on select  cast(case ' +
  'when grouping(DatabaseName) = 0 ' +
  'and grouping(ChangeAction) = 1 ' + 
  'and grouping(VssItem) = 1 ' +
   'then Space(2) + isnull(''db: '' + DatabaseName, ''Jobs'') ' +
  'when grouping(DatabaseName) = 0 ' + 
  'and grouping(ChangeAction) = 0 ' + 
  'and grouping(VssItem) = 1 ' +
   'then char(9) + ChangeAction ' +
  'when grouping(DatabaseName) = 0 ' + 
  'and grouping(ChangeAction) = 0 ' + 
  'and grouping(VssItem) = 0 ' +
   'then char(9) + char(9) + VssItem ' + 
  'when grouping(DatabaseName) = 1 ' + 
  'and grouping(ChangeAction) = 1 ' + 
  'and grouping(VssItem) = 1 ' +
   'then char(32) ' +
  'end as varchar(80)) [DDL Change Report for ' 
                         + cast(getdate() as varchar(11)) 
                         + ' on ' + @@servername + '] ' + 
 'from admin.dbo.archutilchanges ' +
 'where RecCreatedDt > ''' + @Date + ''' ' +
 'and (DatabaseName is not null or Right(VSSItem,4) = ''.JOB'') ' +
 'and VssItemType = ''File'' ' + 
 'group by DatabaseName, ChangeAction, VssItem ' + 
 'with rollup ' +
 'order by grouping(DatabaseName) desc,DatabaseName, ChangeAction, VssItem'

if exists(select 1 from admin.dbo.archutilchanges
  where RecCreatedDt > @Date
  and (DatabaseName is not null or Right(VSSItem,4) = '.JOB')
  and VssItemType = 'File')
 exec sysmon.dbo.safe_sendmail
  @recipients = @Recipients
  , @subject = @Subject
  , @Query = @Query
  , @width = 99

RETURN



GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON 
GO