Code Comparison between two databases

,

This script compares the code (SP, Func. etc.) between two databases (say Dev and QA) and reports the objects which have differences.

/*
  Dev DB: PSI_ESDDEVMAY05 - "REPLACE ALL" with your Dev DB Name
  QA DB: PSI_ESDMAY06     - "REPLACE ALL" with your QA DB Name
*/
SET NOCOUNT ON
DECLARE @ObjectName sysname
       ,@ObjectType char(2)

IF object_id('tempdb..#TheObjects') IS NOT NULL DROP TABLE #TheObjects
CREATE TABLE #TheObjects (ObjectName sysname,ObjectType char(2))
INSERT #TheObjects(ObjectName,ObjectType)
SELECT [name],xtype FROM PSI_ESDDEVMAY05..sysobjects WHERE xtype IN ('P','IF','V','FN','TF')
                                                 AND [name] IN (SELECT [name] FROM PSI_ESDMAY06..sysobjects WHERE xtype IN ('P','IF','V','FN','TF'))
-- DELETE #TheObjects WHERE object_id(ObjectName) IS NULL

IF object_id('tempdb..#DevCode') IS NOT NULL DROP TABLE #DevCode
CREATE TABLE #DevCode (LineNum int IDENTITY NOT NULL,CodeText varchar(8000))

IF object_id('tempdb..#QACode') IS NOT NULL DROP TABLE #QACode
CREATE TABLE #QACode (LineNum int IDENTITY NOT NULL,CodeText varchar(8000))

IF object_id('ProblemObject') IS NOT NULL DROP TABLE ProblemObject
CREATE TABLE ProblemObject (ObjectName sysname,ObjectType char(2))

WHILE EXISTS (SELECT * FROM #TheObjects)
BEGIN
  SELECT TOP 1 @ObjectName = ObjectName,@ObjectType = ObjectType FROM #TheObjects
  INSERT #DevCode EXECUTE PSI_ESDDEVMAY05..sp_helptext @ObjectName
  INSERT #QACode EXECUTE PSI_ESDMAY06..sp_helptext @ObjectName

  DELETE #DevCode WHERE nullif(CodeText,ltrim(rtrim(''))) IS NULL
  DELETE #QACode WHERE nullif(CodeText,ltrim(rtrim(''))) IS NULL

  INSERT ProblemObject (ObjectName,ObjectType) 
  SELECT @ObjectName,@ObjectType WHERE EXISTS(SELECT * FROM #DevCode dev JOIN #QACode qa ON dev.LineNum = qa.LineNum AND dev.CodeText <> qa.CodeText)
  
  TRUNCATE TABLE #DevCode
  TRUNCATE TABLE #QACode
  DELETE #TheObjects WHERE ObjectName = @ObjectName 
END

IF object_id('tempdb..#TheObjects') IS NOT NULL DROP TABLE #TheObjects
IF object_id('tempdb..#DevCode') IS NOT NULL DROP TABLE #DevCode
IF object_id('tempdb..#QACode') IS NOT NULL DROP TABLE #QACode

/*
select 'exec sp_helptext ' + ObjectName,ObjectType from ProblemObject WHERE ObjectName LIKE 'SH%'
*/

Rate

Share

Share

Rate