-- Note: This SP will not work on databases that don't have the same -- collation setting as the server. if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_objects_changed]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[usp_objects_changed] GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO CREATE procedure usp_objects_changed as -- -- This stored procedure was written by Greg Larsen for Washington State Department of Health. -- Date: 08/14/2002 -- -- Description: -- This stored procedure determines which tables, views, constraints, indexes -- stored procedures, triggers, defaults have changed have been changed, and -- sends an email to DBA's to notify them of the change. -- -- Modified: -- Greg Larsen - 11/7/2002 - Modified to support when older versions of objects are restored. -- Section A Begin ------------------------------------------------------------------------------- -- -- Declare variables needed -- declare @CMD varchar(8000) declare @CNT int -- Create temporary table to hold report create table ##temp_text ( email_text char(100)) -- Generate report heading and column headers insert into ##temp_text values('The following objects have changed') insert into ##temp_text values ('Database Object Status') insert into ##temp_text values ('------------------------- -------------------------------------------------- ----------') -- Section A End ----------------------------------------------------------------------------------- -- Section B Begin -------------------------------------------------------------------------------- -- build command to populate report set @CMD = 'insert into ##temp_text (email_text) ' + 'select cast (database_name as char(25)) + '' '' + ' + 'cast(rtrim(f.name) + ''.'' + object_name as char(50)) + '' '' + type_of_change from ' + '(select cast(''?'' as char(30)) as database_name, ' + 'cast(case when a.name is null then b.name else a.name end as char(59)) as object_name, ' + 'case when b.object_create_date is NULL and a.name is not null then ''NEW'' '+ 'when b.dflag = 1 then ''REINSTATED'' ' + 'when b.object_create_date + b.schema_ver > a.crdate + a.schema_ver then ''RESTORED'' ' + 'when b.object_create_date is not null and a.name is not null then ''CHANGED'' ' + 'else ''DELETED'' end as type_of_change, ' + 'case when c.uid is null then b.uid else c.uid end as uid from ' + -- join sysobjects and sysusers you can get the owner of all currently existing objects '?..sysobjects a join ?..sysusers c on a.uid=c.uid ' + -- Perform full outer join so you can have changed and deleted objects 'full outer join ' + -- Get the lastest object_version record for each object '(select z.dbname, z.object_create_date, z.uid, z.name, z.schema_ver, z.dflag from dbo.object_versions z ' + 'where version_date = (select max(version_date) ' + 'from dbo.object_versions where dbname = z.dbname ' + 'and uid = z.uid ' + 'and name = z.name and dbname = ''?'')) b ' + -- join latest object version with sysobject and sysuser data ' on a.name = b.name and ' + 'a.uid=b.uid and b.dbname = ''?'' ' + -- only return rows that have a different version 'where a.crdate + a.schema_ver b.object_create_date + b.schema_ver ' + -- skip tempdb 'and ''?'' ''tempdb'' ' + -- return rows for deleted objects, exclude tempdb 'or ((a.name is null and b.dflag is null and ''?'' ''tempdb'') ' + -- return rows for new objects, exclude tempdb 'or (b.name is null and ''?'' ''tempdb'')))' + -- join rows so deleted object are associated with a object owner ' e join ?..sysusers f on e.uid = f.uid ' print @CMD -- execute command to populate report exec sp_MSforeachdb @CMD -- Section B End --------------------------------------------------------------------------------- -- Get number of records in ##temp_text select @CNT = count(*) from ##temp_text if @CNT > 3 begin -- Section C Begin ------------------------------------------------------------------------------- -- Build Command to insert records into OBJECT_VERSIONS table -- with new changes set @CMD = 'insert into dbo.object_versions ' + '(dbname, name, schema_ver, object_create_date,xtype,uid) ' + 'select ''?'',a.name,a.schema_ver, a.crdate,a.xtype,a.uid from ' + '?..sysobjects a left join' + -- Get the lastest object_version record for each object '(select z.dbname, z.object_create_date, z.uid, z.name, z.schema_ver, z.dflag from dbo.object_versions z ' + 'where version_date = (select max(version_date) ' + 'from dbo.object_versions where dbname = z.dbname ' + 'and uid = z.uid ' + 'and name = z.name and dbname = ''?'')) b ' + ' on a.name = b.name and' + ' a.schema_ver = b.schema_ver and' + ' a.crdate = b.object_create_date and b.dbname = ''?'' ' + -- which objects have a new version ' where a.crdate + a.schema_ver b.object_create_date + b.schema_ver ' + -- Exclude tempdb 'and ''?'' ''tempdb'' ' + -- Return rows for changed objects, exclude tempdb 'or ((a.name is null and ''?'' ''tempdb'') or ' + -- Return rows for new objects, exclude tempdb '(b.name is null and ''?'' ''tempdb''))' -- Insert records into OBJECT_VERSION table print @CMD exec sp_MSforeachdb @CMD -- Section C End --------------------------------------------------------------------------------- -- Section D Begin ------------------------------------------------------------------------------- -- build command to set DFLAG for all deleted objects set @CMD = 'update dbo.object_versions ' + 'set dflag = 1 ' + 'where dbname = ''?'' ' + 'and name in (select b.name ' + 'from ?..sysobjects a join ?..sysusers c ' + -- join all object_versions records with sysobjects 'on a.uid=c.uid right outer join ' + 'dbo.object_versions b ' + 'on a.name = b.name and a.uid=b.uid and b.dbname = ''?'' ' + -- exclude tempdb 'where ''?'' ''tempdb'' ' + -- return only rows for the current database 'and b.dbname = ''?'' ' + -- return rows with no sysobjects (deleted objects) 'and a.name is null ' + -- return only rows that have not been deleted yet 'and b.dflag is null)' -- Update DFLAG for deleted objects print @CMD exec sp_MSforeachdb @CMD -- Section D End --------------------------------------------------------------------------------- -- Section E Begin ------------------------------------------------------------------------------- -- Email report to DBA distribution list exec master.dbo.xp_sendmail @recipients='dba@yourcompany.com', @subject='Objects that have changed', @query='select * from ##temp_text', @no_header='true', @width=150 end -- @CNT > 3 -- Section E End --------------------------------------------------------------------------------- -- Drop temporary table drop table ##temp_text GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO