This post originated from an RSS feed registered with .NET Buzz by Raymond Lewallen. Original Post: Get Notification of Schema Changes in your Sql Server Database via Email Feed Title: Raymond Lewallen Feed URL: /error.htm?aspxerrorpath=/blogs/raymond.lewallen/rss.aspx Feed Description: Patterns and Practices, OOP, .Net and Sql Latest .NET Buzz Posts Latest .NET Buzz Posts by Raymond Lewallen Latest Posts From Raymond Lewallen

Here is a nice little script from a guy who calls himself Shas3.  This procedure sends an email report on table schema changes, new tables and deleted tables since the last time this procedure was run.  Best to set this up on a weekly job if you want to know what’s going on in your database.

create procedure usp_dba_schema_ver_cntrl as
BEGIN
 
  set nocount on

  declare @cmd varchar(8000)
 declare @tbl_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 @subject  varchar(255)
 declare @message varchar(255)
 declare @query   varchar(800)
 
  set @status = 0  -- successful status

 if not exists (select name from sysobjects where name = 'dba_SchemaVerCntrl' and xtype = 'U')
  create table dba_SchemaVerCntrl
  (TableName sysname not null,
   CreateDate datetime not null,
   SchemaVersion int not null)

   select @cnt = count(*) from dba_SchemaVerCntrl
  
 IF @cnt = 0
 BEGIN
  select @msg = 'Have to initialize dba_SchemaVerCntrl table'
  print @msg
  
  insert into dba_SchemaVerCntrl
  select name, Crdate, schema_ver
  from sysobjects
  where xtype = 'U'
  and uid = 1
 END
 ELSE
 BEGIN
  create table ##dba_schema(
  tbl_name sysname not null,
  status char not null,
  description varchar(50) null)

  declare tbl_cursor cursor for
     select name, Crdate, schema_ver
     from sysobjects where xtype = 'U'
  and uid = 1
   
   open tbl_cursor
    fetch next from tbl_cursor into @tbl_name, @current_crdate, @current_ver
    WHILE @@fetch_status = 0
    BEGIN
      -- compare the current schema version against the stored schema version
      select @stored_ver = SchemaVersion, @stored_crdate = CreateDate
      from dba_SchemaVerCntrl
      where TableName = @tbl_name

   IF @@ROWCOUNT = 0 -- no record found, a new table
      BEGIN
        select @msg = ' created on ' + convert(varchar(20), @current_crdate)
        --print @msg
    insert into dba_SchemaVerCntrl
    values (@tbl_name, @current_crdate, @current_ver)
    IF @@ERROR <> 0
         BEGIN
           print 'Error inserting into dba_SchemaVerCntrl'
           set @status = 1
         END
    insert into ##dba_schema
    values (@tbl_name, 'N', @msg)
    IF @@ERROR <> 0
         BEGIN
           print 'Error inserting into ##dba_schema'
           set @status = 1
         END
   
       END
      ELSE
   BEGIN 
       IF @current_crdate <> @stored_crdate or
     @current_ver <> @stored_ver -- values are different
        BEGIN
          -- update stored size value
          update dba_SchemaVerCntrl
          set CreateDate = @current_crdate,
     SchemaVersion = @current_ver
          where TableName = @tbl_name
          IF @@ERROR <> 0
          BEGIN
            print 'Error updating dba_SchemaVerCntrl'
            set @status = 1
          END
     insert into ##dba_schema
     values(@tbl_name, 'U', null)
     IF @@ERROR <> 0
          BEGIN
            print 'Error inserting into ##dba_schema'
            set @status = 1
          END
        END  -- table schema has been changed
        
      END  -- matching record found
  
      fetch next from tbl_cursor into @tbl_name, @current_crdate, @current_ver
    END  -- end loop
    close tbl_cursor
    deallocate tbl_cursor

  -- get a list of deleted objects
  insert into ##dba_schema
  select tablename, 'D', null from dba_SchemaVerCntrl
  where not exists (select * from sysobjects
   where xtype = 'U'
   and uid = 1
   and dba_SchemaVerCntrl.tablename = sysobjects.name)
 
  delete dba_SchemaVerCntrl
  where not exists (select * from sysobjects
   where xtype = 'U'
   and uid = 1
   and dba_SchemaVerCntrl.tablename = sysobjects.name)

  select RTRIM(tbl_name) as 'Table Name',
  case status
  when 'U' then 'Table schema has been changed'
  when 'N' then 'New table ' + RTRIM(description)
  else 'Table has been deleted'
  end as 'Schema Control Status'
  from ##dba_schema
  order by status desc, tbl_name

  IF @@rowcount <> 0  -- send mail
  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 RTRIM(tbl_name) as ''Table Name'',
   case status
   when ''U'' then ''Table schema has been changed''
   when ''N'' then ''New table '' + RTRIM(description)
   else ''Table has been deleted''
   end as ''Schema Control Status''
   from ##dba_schema
   order by status desc, tbl_name'

   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_schema
 
 END  -- @cnt <> 0
   
 IF @status <> 0
    return 1
 
  return 0
END

GO