Notification of schema changes - Advanced

/********************************************************************
Created by:     Shas3	
Original: http://qa.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 @message	varchar(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