Compare SQL code of 2 objects.

,

Scripst are written to use database with name DBAdmin for maintanace. You can use any existing databsae, but you need to change all scripts to use other name then DBAdmin.

You need create table dba_objScript and procedure usp_DBA_objScript_compare in DBAdmin

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 server need to have database DBAdmin and table dba_objScript.

EACH DATABASE, from which you want compare objects need to have stored proc usp_dba_objScript_save.

Only used in SQL2000.

/*********************************************************
** 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

Rate

Share

Share

Rate