Notification of schema changes - Advanced

,

This script modifies another excellent script written by SHAS3. The original script examines tables for changes and sends an email. This modification examines all stored procedures, tables, indexes, etc. in ALL databases or just the CURRENT database. If changes are detected, the script has the option to either email or log to a table that it will create for you. In addition, several performance enhancements are included as well as NOLOCK statements where appropriate to make it production ready.

/********************************************************************
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 @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

Rate

Share

Share

Rate