Compare SQL code of 2 objects.

/*********************************************************
** Name            : usp_DBA_objScript_compare
** Created By    : (LLT)
** Desc            : compares 2 DB objects (Sp, function,triggesr). Ignores comments and spaces/empty lines. Objects can be in diferent servers, DBs, and/or owner
** depend on    : procedure usp_dba_objScript_save need to be added to each Database you need to compare scripts from. Code is below 
** depend on    : table dba_objScript (compareDt datetime,server varchar(128), dbName varchar(128), objName varchar(128), lineNum int, lineText varchar(4000),lineID int identity)
**
**        (c)All rights reserved, S_M_Y_T_H Solutions LLC
*******************************************************************************
REQUIREMENTS:
Scripst are written to use database with name DBAdmin for maintanace.
You need create table dba_objScript and procedure usp_DBA_objScript_compare there
---------------
use dbadmin
CREATE TABLE [dba_objScript] (
    [compareDt] [datetime] NULL ,
    [server] [varchar] (128) ,
    [dbName] [varchar] (128) ,
    [owner] [varchar] (128) ,
    [objName] [varchar] (128) ,
    [lineNum] [int] NULL ,
    [lineText] [varchar] (4000) ,
    [lineID] [int] IDENTITY (1, 1) NOT NULL 
) 

If you need compare objects across servers, you need to have other servers added as linked server on server where 
you run DBAdmin..usp_DBA_objScript_compare is located

EACH DATABASE, from which you want compare objects need to have stored proc usp_dba_objScript_save. Code is below.
*******************************************************************************
**        Sample run.
exec dbadmin.dbo.usp_DBA_objScript_compare @objName1='proc_SND_customerProfile_save',@objName2='proc_SND_customerProfile_save150'
    ,@dbname1='smyth',@dbname2='tarnell',@server1='production-sql',@server2='developement-sql'

it will Compares objects
[production-sql].smyth.dbo.proc_SND_customerProfile_save 
and    
[developement-sql].tarnell.dbo.proc_SND_customerProfile_save150

********************************************************************************/
--drop proc usp_DBA_objScript_compare
create proc usp_DBA_objScript_compare
--declare 
    @objName1 varchar(128),@objName2 varchar(128)=null -- object names to compare, use the sa
    ,@server1 varchar(128)=null, @server2 varchar(128)=null
    ,@dbname1 varchar(128)=null, @dbname2 varchar(128)=null
    ,@owner1 varchar(128)=null, @owner2 varchar(128)=null
as

set nocount on
--set ansi_warnings off
declare @sql nvarchar(4000), @scriptDt datetime
/*
select
    @server1='bsands-sql', @server2='bsands-web'
    ,@owner1='dbo', @owner2='dbo'
    ,@dbname1='sandsProd', @dbname2='sandsProd'
    ,@objName1='proc_SND_RatingHistory_add',    @objName2='proc_SND_RatingHistory_add'
*/set @scriptDt = dateadd(mi,-5,getdate())
if isnull(@server1,'') = ''        set @server1    = @@servername
if isnull(@dbname1,'') = ''        set @dbname1    = db_name()
if isnull(@owner1,'') = ''        set @owner1        = user

if isnull(@server2,'') = ''        set @server2    = @server1
if isnull(@owner2,'') = ''        set @owner2        = @owner1
if isnull(@dbname2,'') = ''        set @dbname2    = @dbname1
if isnull(@objName2,'') = ''    set @objName2    = @objName1


declare @srvName1 varchar(128), @srvName2 varchar(128)
declare @id1 varchar(200),@id2 varchar(200)
set @id1=@server1+'.'+@dbname1+'.'+@owner1+'.'+@objName1
set @id2=@server2+'.'+@dbname2+'.'+@owner2+'.'+@objName2
-- script obj1 in its server
select @sql = 
    'exec ['+@server1+'].['+@dbname1+'].['+@owner1+'].usp_dba_objScript_save @objName='''+@objName1+''',@owner='''+@owner1+''''
print @sql
exec sp_executesql @sql

-- script obj2 in its server
select @sql = 
    'exec ['+@server2+'].['+@dbname2+'].['+@owner2+'].usp_dba_objScript_save @objName='''+@objName2+''',@owner='''+@owner2+''''
print @sql
exec sp_executesql @sql

declare @ctStart int, @clStart int, @ctEnd int, @clEnd int
declare @cId int

create table #s1 ( lineNum int, lineText varchar(7500), lineCS bigint,lineID int identity)
set @sql ='insert into #s1(lineText) select ltrim(left(lineText,7500)) from ['
    +@server1+'].dbadmin.dbo.dba_objScript '
    +'where server='''+isnull(@srvName1,@server1)
    +''' and owner='''+@owner1+''' and dbname='''+@dbname1+''' and objName='''+@objName1+''' order by lineID'
--print @sql
exec sp_executesql @sql

create table #s2 ( lineNum int, lineText varchar(7500), lineCS bigint,lineID int identity)
set @sql ='insert into #s2(lineText) select ltrim(left(lineText,7500)) from ['
    +@server2+'].dbadmin.dbo.dba_objScript '
    +'where server='''+isnull(@srvName2,@server2)
    +''' and owner='''+@owner2+''' and dbname='''+@dbname2+''' and objName='''+@objName2+''' order by lineID'
--print @sql
exec sp_executesql @sql


update #s1 set lineText= dbcommon.dbo.fVal_trimSpaces(lineText)
update #s2 set lineText= dbcommon.dbo.fVal_trimSpaces(lineText)

-- delete one line comments
delete from #s1 where lineText like '--%' 
delete from #s1 where lineText like '/*%*/' 
delete from #s1 where len(lineText)=0 
declare @rcnt int
set @rcnt=1
while (@rcnt>0)
begin
    update #s1 set lineText = substring(lineText,1,charindex('/*', lineText)-1) 
    + substring(lineText,charindex('*/', lineText,charindex('/*', lineText))+2, len(lineText))
    where charindex('/*', lineText)>0 and charindex('*/', lineText,charindex('/*', lineText)) >0 
    and charindex('/*', lineText)< charindex('*/', lineText,charindex('/*', lineText))
    set @rcnt=@@rowcount
end
-- delete multi-line comments

select @ctStart =0, @clStart =1, @ctEnd =0, @clEnd =0
while (@clStart>0)
begin
    set @clStart=0
    -- find start of the comment
    select top 1 @clStart=lineID,@ctStart=charindex('/*', lineText)-1 from #s1 
        where charindex('/*', lineText)>0 and lineID> @clEnd
        order by lineId
    if @@rowcount=0
        break
    -- find end of the comment
    if (@clStart > 0)
    begin
        select @clEnd = 0, @ctEnd=0
        select top 1 @clEnd=lineID,@ctEnd= charindex('*/', lineText)+2 from #s1 
        where charindex('*/', lineText)>0 and lineID> @clStart 
        order by lineId
    end
    if (@clEnd > 0) begin
        update #s1 set lineText=substring(lineText, 1, @ctStart )+'/*...' where lineID=@clStart
        delete from #s1 where lineID>@clStart and lineID < @clEnd
        update #s1 set lineText= '...*/'+substring(lineText, @ctEnd, len(lineText)-@ctEnd+5) where lineID=@clEnd
    end
    else
        break
    --print str(@clStart)+str(@clEnd)+str(@ctStart)+str(@ctEnd)
end

-- renumber
set @cId =1
update #s1 set lineNum=@cid, @cid=@cid+1

------------- s2

-- delete one line comments
delete from #s2 where lineText like '--%' 
delete from #s2 where lineText like '/*%*/' 
delete from #s2 where len(lineText)=0 
set @rcnt=1
while (@rcnt>0)
begin
    update #s2 set lineText = substring(lineText,1,charindex('/*', lineText)-1) 
    + substring(lineText,charindex('*/', lineText,charindex('/*', lineText))+2, len(lineText))
    where charindex('/*', lineText)>0 and charindex('*/', lineText,charindex('/*', lineText)) >0 
    and charindex('/*', lineText)< charindex('*/', lineText,charindex('/*', lineText))
    set @rcnt=@@rowcount
end

-- delete multi-line comments

select @ctStart =0, @clStart =1, @ctEnd =0, @clEnd =0
while (@clStart>0)
begin
    set @clStart=0
    -- find start of the comment
    select top 1 @clStart=lineID,@ctStart=charindex('/*', lineText)-1 from #s2 
        where charindex('/*', lineText)>0 and lineID> @clEnd
        order by lineId
    if @@rowcount=0
        break
    -- find end of the comment
    if (@clStart > 0)
    begin
        select @clEnd = 0, @ctEnd=0
        select top 1 @clEnd=lineID,@ctEnd= charindex('*/', lineText)+2 from #s2
        where charindex('*/', lineText)>0 and lineID> @clStart 
        order by lineId
    end
    if (@clEnd > 0) begin
        update #s2 set lineText=substring(lineText, 1, @ctStart )+'/*...' where lineID=@clStart
        delete from #s2 where lineID>@clStart and lineID < @clEnd
        update #s2 set lineText= '...*/'+substring(lineText, @ctEnd, len(lineText)-@ctEnd+5) where lineID=@clEnd
    end
    else
        break
    --print str(@clStart)+str(@clEnd)+str(@ctStart)+str(@ctEnd)
end

-- renumber
set @cId =1
update #s2 set lineNum=@cid, @cid=@cid+1

----- end s2 ------
delete from #s1 where len(ltrim(rtrim(lineText)))=0 
delete from #s2 where len(ltrim(rtrim(lineText)))=0 

--Now compare only lineCS
update #s1 set lineCS=checksum(replace(lineText,' ',''))
update #s2 set lineCS=checksum(replace(lineText,' ',''))

-- search were first identical line after comments
declare @SC1 int, @SC2 int
select @SC1=0, @SC2=0
select top 1 @SC1=s1.lineNum,@SC2=s2.lineNum from #s1 s1 join #s2 s2 on s1.lineCS=s2.lineCS where s1.lineText not in ('/*...','...*/')
    order by s1.lineNum, s2.lineNum 

create table #tblRet (idd int, files varchar(80),lineId1 int, object1 varchar(2000),lineId2 int, object2 varchar(2000),ordr int)
    select @sql=
    'insert into #tblRet select 1 idd,''<--->'',0 lineid1, '''+@id1+''', 0 lineid2, '''+@id2+''' [object2],-6'
    exec sp_executesql @sql

    insert into #tblRet (idd, files,object1,object2, ordr) values(1,'','Different lines','',-5)
    insert into #tblRet (idd, files,object1,object2, ordr) values(1,'','Identical Lines','',-4)
if (@SC1>1 or @SC2 >1 )
begin
-- select code that different at the beginng into results
    insert into #tblRet (idd, files,object1,object2, ordr) values(1,'----','---Difference at the beginning ---','-----------',-3)
    select @sql=
    'insert into #tblRet select 2 idd,''<---'',lineID , linetext [object1], 0, '' '' [object2],-2'
    +' from #s1 where lineNum<'+str(@SC1)+' order by lineId'
    exec sp_executesql @sql

    select @sql=
    'insert into #tblRet select 3 idd,''--->'',null,null,lineID , linetext [object2],-1'
    +' from #s2 where lineNum<'+str(@SC2)+' order by lineId'

--    print '1.'+@sql
    exec sp_executesql @sql

    delete from #s1 where lineNum < @SC1
    delete from #s2 where lineNum < @SC2
end

-- code that different at the begining

-- renumber
set @cId =1
update #s1 set lineNum=@cid, @cid=@cid+1
set @cId =1
update #s2 set lineNum=@cid, @cid=@cid+1


-- mid/end code difference
declare @ls1 int, @ls2 int
select @ls1=max(lineNUm) from #s1
select @ls2=max(lineNUm) from #s2
--select @ls1,@ls2
-- where for mid code difference
declare @where varchar(1000), @cnt int, @order varchar(100), @lDiff varchar(20)
    , @sign1 varchar(10), @sign2 varchar(10), @step int
--select @ls1=12, @ls2=10
select @sign1=case when @ls1-@ls2>0 then '=' else '+' end
select @sign2=case when @ls1-@ls2>0 then '+' else '=' end

select @where ='s1.lineNum=s2.lineNum ', @cnt=@ls1-@ls2
while (abs(@cnt) >0) begin
    set @step= abs((@ls1-@ls2)-@cnt)+1
    set @lDiff= @sign1+ltrim(str(@step))+@sign2
    select @where = @where +' or s1.lineNum'+@lDiff+'s2.lineNum ', @cnt=sign(@cnt)*(abs(@cnt)-1)
end

--select @where, @order
--select * from #s1
--select * from #s2

--print 'compare '+@id1 +' and '+@id2

    insert into #tblRet (idd, files,object1,object2, ordr) values(3,'-----','----Identical code starts----','',0)
    
    select @sql=
+char(10)+'insert into #tblRet select 4 idd,case when s2.lineID is null then ''<--'' when s1.lineID is null then ''-->'' else ''match'' end'
+char(10)+',s1.lineID, isnull(s1.linetext,'''') [server1], s2.lineID, isnull(s2.linetext,'''') [server2],isnull(s1.lineNum, s2.lineNUm) idd2'
+char(10)+' from #s1 s1    full join #s2 s2 on s1.lineCS=s2.lineCS'
+char(10)+' and ('+@where+')'
+char(10)+' order by idd,idd2 '

print @sql
exec sp_executesql @sql

select @SC1=count(*) from #tblRet where files='<--'
select @SC2=count(*) from #tblRet where files='-->'
select @cId=count(*) from #tblRet where files in ('match')
select @ls1=count(*) from #tblRet where files in ('<--','-->')

update #tblRet set lineId1=@SC1,lineId2=@SC2 where files='<---->' 
update #tblRet set lineId1 = @ls1 where object1='Different lines' 
update #tblRet set lineId1 = @cId where object1='Identical lines' 


select * from #tblRet order by idd, ordr

set nocount off


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

----------------------------------
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



/*************************************
** saves object code in DB
Runs in current DB ONLY
*************************************/create proc usp_DBA_objScript_save
    @objName varchar(128)
    ,@owner varchar(128) = 'dbo'
as
begin
set nocount on
declare     @dbname varchar(128)    ,@server varchar(128)    , @fullObjName varchar(512)


    select @dbName=db_name()
            ,@server = @@servername
            --,@objName = 'proc_SND_reportPrice_save'
    
    select @fullObjName = @dbname+'.'+@owner+'.'+@objName
    --select @fullObjName
        delete from dbadmin.dbo.dba_objScript where server=@server and dbName=@dbname and objName=@objName and owner=@owner
        insert into dbadmin.dbo.dba_objScript (lineText)
            EXEC sp_helptext @fullObjName

        update dbadmin.dbo.dba_objScript set 
            server=@server, dbName=@dbname, owner=@owner, objName=@objName
            ,compareDt=getdate()
            where compareDt is null and objName is null


set nocount off
end


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO