Use the following script to create a stored procedure that will compare objects in two databases and then report on objects which are different. Ideal usage is for use during Acclamare updates.
1) Restore {live} DB into a ObjectTest DB (remove (TKOCompany in ObjectTest)
2) Run the procedure "SP_SCRIPTDIFF '{live}', 'ObjectTest'
This will provide and indicator of objects which changed in the update fro which you will need to re-implement modified procs.
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE Specific_name = 'SP_SCRIPTDIFF') DROP PROCEDURE DBO.SP_SCRIPTDIFF GO CREATE PROCEDURE [dbo].SP_SCRIPTDIFF @DBNAME1 SYSNAME, @DBNAME2 SYSNAME AS /* OBJECTIVE : TO COMPARE THE FUNCTIONS, PROCEDURES AND TRIGGERS IN TWO DIFFERENT DATABASES, PASS NAME OF DATABASE1, AND DATABASE2 AS INPUTS. */ BEGIN SET NOCOUNT ON Exec ('select DISTINCT O1.name as [ObjectName], O1.modify_date As DateIn_'+@DBNAME1+', O2.modify_date As DateIn_'+@DBNAME2+',o1.type as Type from '+ @DBNAME1+'.sys.all_objects O1 join '+ @DBNAME2+'.sys.all_objects O2 on O1.name = O2.name and O1.type = O2.type join '+ @DBNAME1+'.sys.syscomments C1 on O1.object_id = C1.id join '+ @DBNAME2+'.sys.syscomments C2 on O2.object_id = C2.id join '+ @DBNAME1+'.sys.schemas S1 on O1.schema_id = S1.schema_id join '+ @DBNAME2+'.sys.schemas S2 on O2.schema_id = S2.schema_id where C1.text <> C2.text and c1.colid = c2.colid and O1.Type in (''FN'',''P'',''T'') And o1.Is_Ms_Shipped = 0 Order by O1.type,ObjectName') RETURN END GO
exec dbo.SP_ScriptDiff 'SWPCFS', 'ObjectTest' select * from ET_Modifications
CREATE TABLE [dbo].[ET_Modifications]( [ID] [int] IDENTITY(1,1) NOT NULL, [Object] [varchar](100) NULL, [Notes] [varchar](2000) NULL, [DateAdded] [date] NULL, [Active] [varchar](1) NOT NULL ) ON [PRIMARY] GO