SQL compare objects

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
Back to Home