Notification of schema changes - Advanced

/********************************************************************
Created by:     Shas3
Original: http://www.sqlservercentral.com/scripts/contributions/817.asp

Rewritten by: Brian Corrigan

Change Log:
9/28/2005 - Included log to table option, removed object cursor to improve efficiency, now includes option to examine all databases, examines all objects and not just tables

********************************************************************/

create procedure usp_dba_schema_ver_cntrl as
BEGIN 
 set nocount on

 declare @cmd varchar(8000)
declare @obj_name sysname
declare @db_name sysname
 declare @current_ver int
 declare @stored_ver int
 declare @current_crdate datetime
 declare @stored_crdate datetime
declare @cnt int
 declare @msg varchar(600)
 declare @status smallint
declare @logtoemail int
declare @logtotable int
declare @examinealldatabases int
 set @status = 0  -- successful status
declare @audittime datetime
set @audittime=current_timestamp

declare@subject varchar(255)
declare @messagevarchar(255)
declare @query  varchar(800)


--CONFIGURATION OPTIONS
--EMAIL and or SQL TABLE logging of results; 1 = YES, 0 = NO
set @logtoemail=0
set @logtotable=1

--ALL DATABASES or just CURRENT DATABASE; 1 = ALL, 0 = CURRENT
set @examinealldatabases = 1


if not exists (select name from sysobjects (NOLOCK) where name = 'dba_SchemaVerCntrl' and xtype = 'U')
create table dba_SchemaVerCntrl
([db_Name] sysname not null,
Obj_Name sysname not null,
 CreateDate datetime not null, 
 SchemaVersion int not null)
if @logtotable=1 
begin
if not exists (select name from sysobjects (NOLOCK) where name = 'dba_SchemaVerCntrlHistory' and xtype = 'U')
create table dba_SchemaVerCntrlHistory(
SummaryDate datetime, 
[db_Name] sysname not null,
Obj_Name sysname not null,
status char not null,
description varchar(50) null)
end


select @cnt = count(*) from dba_SchemaVerCntrl

IF @cnt = 0 and @examinealldatabases = 0
BEGIN
select @msg = 'Initializing dba_SchemaVerCntrl table with information from current DB'
print @msg

insert into dba_SchemaVerCntrl
select db_name(),name, Crdate, schema_ver 
from sysobjects (NOLOCK)

END
ELSE IF @cnt = 0 and @examinealldatabases = 1
BEGIN
select @msg = 'Initializing dba_SchemaVerCntrl table with information from all DBs'
print @msg

declare db_cursor cursor for 
select name from master..sysdatabases (NOLOCK)
WHERE name NOT IN ('master', 'model', 'tempdb', 'msdb', 'distribution') 

open db_cursor
fetch next from db_cursor into @db_name

WHILE @@fetch_status = 0
  BEGIN
select @query = 
'insert into dba_SchemaVerCntrl ' +
'select '''+@db_name+''', name, Crdate, schema_ver ' +
'from '+ @db_name +'..sysobjects (NOLOCK)'
Exec (@query)

fetch next from db_cursor into @db_name
END
  close db_cursor
  deallocate db_cursor

END
ELSE
BEGIN
--Create a temp table to hold all DB current schema
if exists (select name from sysobjects (NOLOCK) where name = '#dba_SchemaVerCntrl' and xtype = 'U')
drop table #dba_SchemaVerCntrlTemp

create table #dba_SchemaVerCntrlTemp
([db_Name] sysname not null,
Obj_Name sysname not null,
 CreateDate datetime not null, 
 SchemaVersion int not null)

--Populate the temp table with sysobjects from all databases
declare db_cursor cursor for 
select name from master..sysdatabases (NOLOCK)
WHERE name NOT IN ('master', 'model', 'tempdb', 'msdb', 'distribution') 

open db_cursor
fetch next from db_cursor into @db_name

WHILE @@fetch_status = 0
BEGIN
select @query = 
'select '''+@db_name+''', name, Crdate, schema_ver ' +
'from '+ @db_name +'..sysobjects (NOLOCK)'

insert into #dba_SchemaVerCntrlTemp
Exec (@query)

fetch next from db_cursor into @db_name
END
  close db_cursor
  deallocate db_cursor

--Create a temp table to record shema changes
declare @dba_SchemaVerCntrlChanges table
([db_Name] sysname not null,
Obj_Name sysname not null,
 CreateDate datetime not null, 
 SchemaVersion int not null,
changetype char
)

insert into @dba_SchemaVerCntrlChanges
--Find all new Records
select st.[db_name], st.obj_name, st.createdate, st.schemaversion, 'N'
from #dba_SchemaVerCntrlTemp st (NOLOCK)
left join dba_SchemaVerCntrl s (NOLOCK)
   on  st.[db_name]=s.[db_name] 
 and st.[obj_name]=s.[obj_name] 
where isnull(s.db_name,'')=''
UNION ALL
--Find all updated Records
select st.[db_name], st.obj_name, st.createdate, st.schemaversion, 'U'
from #dba_SchemaVerCntrlTemp st (NOLOCK)
join dba_SchemaVerCntrl s (NOLOCK)
   on  st.[db_name]=s.[db_name] 
 and st.[obj_name]=s.[obj_name] 
 and st.[schemaversion]<>s.[schemaversion]
UNION ALL
--Find all deleted Records
select s.[db_name], s.obj_name, s.createdate, s.schemaversion, 'D'
from #dba_SchemaVerCntrlTemp st (NOLOCK)
right join dba_SchemaVerCntrl s (NOLOCK)
   on  st.[db_name]=s.[db_name] 
 and st.[obj_name]=s.[obj_name] 
where isnull(st.db_name,'')=''


--Commit all changes to master schema version table, update history is log to table is on
begin transaction
--Update master shema version table for all changed objects
delete from dba_SchemaVerCntrl
insert into dba_SchemaVerCntrl
select * from #dba_SchemaVerCntrlTemp (NOLOCK)

--Record changes in history table
if @logtotable=1 
begin
insert into dba_SchemaVerCntrlHistory
select 
@audittime, 
[db_Name], 
Obj_Name, 
changetype,
case changetype 
 when 'N' then 'Created on ' + convert(varchar(24),createdate,109)
 when 'U' then 'Updated on ' + convert(varchar(12),@audittime,109)
 when 'D' then 'Deleted on ' + convert(varchar(12),@audittime,109)
 else 'Error'
end
from @dba_SchemaVerCntrlChanges
end 
commit transaction

--If log to email is on send the notification
IF @logtoemail=1 and (select count(*) from @dba_SchemaVerCntrlChanges) <> 0 
BEGIN
SELECT @subject = @@SERVERNAME + ' Database ' + DB_Name() +  ': Schema Control Report for ' + convert( varchar(20), GETDATE()) + char(34)
SELECT @message = @@SERVERNAME + ' Database ' + DB_Name() + ': Please find attached the Schema Control Report '

select @query = 
'select 
@audittime as AuditDate, 
[db_Name] as ''DatabaseName'', 
Obj_Name as ''Object Name'', 
case changetype 
 when ''N'' then ''Created on '' + convert(varchar(24),createdate,109)
 when ''U'' then ''Updated on '' + convert(varchar(12),@audittime,109)
 when ''D'' then ''Deleted on '' + convert(varchar(12),@audittime,109)
 else ''Error''
end
from @dba_SchemaVerCntrlChanges
order by [db_name], obj_name, changetype'

EXEC @status = master..xp_sendmail 
     @recipients = '<recipients>'
    ,@message = @message
    ,@subject = @subject
    ,@query   = @query
    ,@attach_results = 'false'
    ,@no_header = 'false'
    ,@echo_error = 'true'
    ,@width = 300
END  -- end send mail

drop table #dba_SchemaVerCntrlTemp
  
END  -- @cnt <> 0
 

IF @status <> 0
  return 1
 
 return 0
END


GO