Compare the structure & code of 2 databases
We often make changes in the test databases. But failure in making even the smallest change in the production database may lead to unwanted situations.
This utility compares 2 databases.
Parameters to be passed
@DB1 Database 1 (SysName)
@DB2 Databse 2 (SysName)
@ShowDifferentOnly see later (Bit)
Parameters 1, 2 may include server name as well.
But the server should be either local or linked server.
This Procedure is devided into 2 parts
Part I Checks the tables and Views (Column Definition, size, default) etc.
Part II Checks the Code of Views, Stored Procedures and Triggers
If @ShowDifferentOnly is set to 1 it will show only the lines that are different
Otherwise all lines of both objects are shown.
Default for @ShowDifferentOnly is 1
Tested in SQLServer 2000 Service Pack 1.
Known Issues:
If Server name or the database name is with space, it should be included with single Quote and not with braces ([]). This procedure is not checking for the braces in the name.
Note:
This procedure does not check the data. Only the structure is checked.
Use Master
IF EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME = 'sp_Compare2DB' and ObjectProperty(ID, 'ISPROCEDURE')=1)
DROP PROCEDURE sp_Compare2DB
GO
-- sp_Compare2DB
--
-- Compares the structure of 2 databases of SQL Server
-- parameters to be passed
-- @DB1 Database 1 (SysName)
-- @DB2 Databse 2 (SysName)
-- @ShowDifferentOnly see later (Bit)
--
-- Parameters 1, 2 may include server name as well.
-- But the server should be either local or linked server.
-- This Procedure is devided into 2 parts
-- Part I Checks the tables and Views (Column Definition, size, default) etc.
-- Part II Checks the Code of Views, Stored Procedures and Triggers
-- If @ShowDifferentOnly is set to 1 it will show only the lines that are different
-- Otherwise all lines of both objects are shown.
-- Default for @ShowDifferentOnly is 1
--
-- Tested in SQLServer 2000 Service Pack 1.
--
-- Known Issues:
-- 1. If Server name or the database name is with space,
-- it should be included with single Quote and not with braces.
-- This procedure is not checking for the braces in the name.
-- 2. This procedure is not checking the following:
-- Indexes
-- Primary Keys
-- Foreign Keys
-- Contraints
-- rules
-- user deined data types
-- Formula differents for computed columns
--
-- Note:
-- The purpose of this procedure is not to check the data. Only the structure is checked.
--
--
--------------------------------------------------------------------------------------------
-- Written By G.R. Preethiviraj Kulasingham
-- Last Modified on 26th March 2002.
--------------------------------------------------------------------------------------------
CREATE Procedure sp_Compare2DB
@DB1 sysname, @DB2 sysname, @ShowDifferentOnly bit =1
AS
Declare @SQL varchar(8000),
@Text nvarchar(4000),
@BlankSpaceAdded int,
@BasePos int,
@CurrentPos int,
@TextLength int,
@LineId int,
@AddOnLen int,
@LFCR int,
@DefinedLength int,
@SyscomText nvarchar(4000),
@Line nvarchar(1000),
@ProcID int,
@ObjID int,
@ObjID2 int,
@OldProcID int,
@DBName1 SysName,
@SvrName1 SysName,
@DBName2 SysName,
@SvrName2 SysName
Set NoCount on
SET @DefinedLength = 1000
SET @BlankSpaceAdded = 0
-- This Part Validated the Input parameters
set @DB1 = RTRIM(LTRIM(@DB1))
set @DB2 = RTRIM(LTRIM(@DB2))
set @SvrName1 = @@SERVERNAME
IF CHARINDEX('.',@DB1) > 0
begin
set @SvrName1 = LEFT(@DB1,CHARINDEX('.',@DB1)-1)
if not exists (select * from master.dbo.sysservers where srvname = @SvrName1)
begin
print 'There is no linked server named '+@SvrName1+' Found. Precedure Terminates.'
return
end
set @DBName1 = RIGHT(@DB1,LEN(@DB1)-CHARINDEX('.',@DB1))
end
else
set @DBName1 = @DB1
exec ('declare @Name sysname select @Name=name from ['+@SvrName1+'].master.dbo.sysdatabases where name = '''+@DBName1+'''')
if @@rowcount = 0
begin
print 'There is no database named '+@DB1+' Found. Precedure Terminates.'
return
end
set @SvrName2 = @@SERVERNAME
if CHARINDEX('.',@DB2) > 0
begin
set @SvrName2 = LEFT(@DB2,CHARINDEX('.',@DB2)-1)
if not exists (select * from master.dbo.sysservers where srvname = @SvrName2)
begin
print 'There is no linked server named '+@SvrName2+' Found. Precedure Terminates.'
return
end
set @DBName2 = RIGHT(@DB2,LEN(@DB2)-CHARINDEX('.',@DB2))
end
else
set @DBName2 = @DB2
exec ('declare @Name sysname select @Name=name from ['+@SvrName2+'].master.dbo.sysdatabases where name = '''+@DBName2+'''')
if @@rowcount = 0
begin
print 'There is no database named '+@DB2+' Found. Precedure Terminates.'
return
end
IF @DB1=@DB2
BEGIN
PRINT 'Both databases should be different. Precedure Terminates.'
RETURN
END
--End of validation
create Table #Procedures
(
DBName sysname NOT NULL,
UserName sysname not null,
ProcName sysname NOT NULL,
Type varchar(2) NOT NULL,
ObjID int NOT NULL,
ObjID2 int NULL,
ProcID int Identity(1,1) NOT NULL
)
Create Table #ProcText
(
ProcID int NOT NULL,
ColID int NOT NULL,
PText nvarchar(4000) NULL
)
Create Table #ProcLineText
(
ProcID int NOT NULL,
LineID int NOT NULL,
LineText nvarchar(1000) NULL
)
Create Table #TableColumns
(
TABLE_CATALOG sysname NOT NULL,
TABLE_SCHEMA sysname NOT NULL,
TABLE_NAME sysname NOT NULL,
COLUMN_NAME sysname NOT NULL,
ORDINAL_POSITION smallint NOT NULL,
COLUMN_DEFAULT nvarchar(2000) NULL,
IS_NULLABLE bit NOT NULL,
DATA_TYPE sysname NOT NULL,
CHARACTER_MAXIMUM_LENGTH int NULL,
CHARACTER_OCTET_LENGTH int NULL,
NUMERIC_PRECISION tinyint NULL,
NUMERIC_PRECISION_RADIX smallint NULL,
NUMERIC_SCALE int NULL,
DATETIME_PRECISION smallint NULL,
IS_COMPUTED bit NOT NULL,
IS_IDENTITY bit NOT NULL,
IDENTITY_SEED int NULL,
IDENTITY_INCR int NULL,
IS_FORREPL bit NOT NULL,
IS_ROWGUID bit NOT NULL,
IS_DEFAULT bit NOT NULL,
IS_MAXIMUM bit NOT NULL,
IS_OCTET bit NOT NULL,
IS_PRECISION bit NOT NULL,
IS_RADIX bit NOT NULL,
IS_SCALE bit NOT NULL,
IS_DATETIME bit NOT NULL
)
----------------------------------------------------------------------------------------
-- Part I
----------------------------------------------------------------------------------------
-- Get the tables with column data
set @SQL = 'Insert into #TableColumns
SELECT '''+@DB1 +''',
usr.name, obj.name,
Col.name,
col.colid,
com.text,
col.isnullable,
spt_dtp.LOCAL_TYPE_NAME,
convert(int, OdbcPrec(col.xtype, col.length, col.xprec) + spt_dtp.charbin),
convert(int, spt_dtp.charbin +
case when spt_dtp.LOCAL_TYPE_NAME in (''nchar'', ''nvarchar'', ''ntext'')
then 2*OdbcPrec(col.xtype, col.length, col.xprec)
else OdbcPrec(col.xtype, col.length, col.xprec)
end),
nullif(col.xprec, 0),
spt_dtp.RADIX,
col.scale,
spt_dtp.SQL_DATETIME_SUB,
col.iscomputed,
col.colstat,
NULL,
NULL,
0,
0,
0,
0,
0,
0,
0,
0,
0
from ['+@SvrName1+'].['+@DBName1+'].dbo.sysobjects obj,
['+@SvrName1+'].master.dbo.spt_datatype_info spt_dtp,
['+@SvrName1+'].['+@DBName1 +'].dbo.systypes typ,
['+@SvrName1+'].['+@DBName1 +'].dbo.sysusers usr,
['+@SvrName1+'].['+@DBName1 +'].dbo.syscolumns col
LEFT OUTER JOIN
['+@SvrName1+'].['+@DBName1 +'].dbo.syscomments com on col.cdefault = com.id
AND com.colid = 1
WHERE
obj.id = col.id
AND obj.uid=usr.uid
AND typ.xtype = spt_dtp.ss_dtype
AND (spt_dtp.ODBCVer is null or spt_dtp.ODBCVer = 2)
AND obj.xtype in (''U'', ''V'')
AND col.xusertype = typ.xusertype
AND (spt_dtp.AUTO_INCREMENT is null or spt_dtp.AUTO_INCREMENT = 0)'
Execute (@SQL)
set @SQL = 'Insert into #TableColumns
SELECT '''+@DB2 +''',
usr.name, obj.name,
Col.name,
col.colid,
com.text,
col.isnullable,
spt_dtp.LOCAL_TYPE_NAME,
convert(int, OdbcPrec(col.xtype, col.length, col.xprec) + spt_dtp.charbin),
convert(int, spt_dtp.charbin +
case when spt_dtp.LOCAL_TYPE_NAME in (''nchar'', ''nvarchar'', ''ntext'')
then 2*OdbcPrec(col.xtype, col.length, col.xprec)
else OdbcPrec(col.xtype, col.length, col.xprec)
end),
nullif(col.xprec, 0),
spt_dtp.RADIX,
col.scale,
spt_dtp.SQL_DATETIME_SUB,
col.iscomputed,
col.colstat,
NULL,
NULL,
0,
0,
0,
0,
0,
0,
0,
0,
0
from ['+@SvrName2+'].['+@DBName2 +'].dbo.sysobjects obj,
['+@SvrName2+'].master.dbo.spt_datatype_info spt_dtp,
['+@SvrName2+'].['+@DBName2 +'].dbo.systypes typ,
['+@SvrName2+'].['+@DBName2 +'].dbo.sysusers usr,
['+@SvrName2+'].['+@DBName2 +'].dbo.syscolumns col
LEFT OUTER JOIN
['+@SvrName2+'].['+@DBName2 +'].dbo.syscomments com on col.cdefault = com.id
AND com.colid = 1
WHERE
obj.id = col.id
AND obj.uid=usr.uid
AND typ.xtype = spt_dtp.ss_dtype
AND (spt_dtp.ODBCVer is null or spt_dtp.ODBCVer = 2)
AND obj.xtype in (''U'', ''V'')
AND col.xusertype = typ.xusertype
AND (spt_dtp.AUTO_INCREMENT is null or spt_dtp.AUTO_INCREMENT = 0)'
Execute (@SQL)
SET @sql ='UPDATE #TableColumns SET IDENTITY_SEED =A.seed, IDENTITY_INCR = Incr
From
(Select usr.name as table_Schema, obj.name as table_name,
Seed =ident_seed(''['+@DBName2+'].''+usr.Name+''.''+obj.name),
Incr =ident_incr(''['+@DBName2+'].''+usr.name+''.''+obj.name)
From [' + @SvrName2 + '].['+@DBName2+'].dbo.sysobjects obj,
[' + @SvrName2 + '].['+@DBName2+'].dbo.sysusers usr
WHERE obj.uid = usr.uid and
ident_seed(''['+@DBName2+'].''+usr.Name+''.''+obj.name) is not NULL) A
WHERE A.table_Schema=#TableColumns.Table_Schema and
A.table_name = #TableColumns.Table_Name and
#TableColumns.IS_IDENTITY & 1 = 1 and
#TableColumns.Table_catalog ='''+@DB2+''''
Execute (@SQL)
SET @sql ='UPDATE #TableColumns SET IDENTITY_SEED =A.seed, IDENTITY_INCR = A.Incr
From
(Select table_Schema=usr.name, table_name=obj.name,
Seed =ident_seed(''['+@DBName1+'].''+usr.Name+''.''+obj.name),
Incr =ident_incr(''['+@DBName1+'].''+usr.name+''.''+obj.name)
From [' + @SvrName1 + '].['+@DBName1+'].dbo.sysobjects obj,
[' + @SvrName1 + '].['+@DBName1+'].dbo.sysusers usr
WHERE obj.uid = usr.uid and
ident_seed(''['+@DBName1+'].''+usr.Name+''.''+obj.name) is NOT NULL) A
WHERE A.table_Schema= #TableColumns.Table_Schema and
A.table_name = #TableColumns.Table_Name and
#TableColumns.IS_IDENTITY & 1 = 1 and
#TableColumns.Table_Catalog ='''+@DB1+''''
Execute (@SQL)
Update #TableColumns SET IS_DEFAULT =1 Where COLUMN_DEFAULT IS NOT NULL
Update #TableColumns SET IS_OCTET = 1 Where CHARACTER_OCTET_LENGTH IS NOT NULL
Update #TableColumns SET IS_RADIX = 1 Where NUMERIC_PRECISION_RADIX IS NOT NULL
Update #TableColumns SET IS_MAXIMUM = 1 Where CHARACTER_MAXIMUM_LENGTH IS NOT NULL
Update #TableColumns SET IS_PRECISION = 1 Where NUMERIC_PRECISION IS NOT NULL
Update #TableColumns SET IS_SCALE = 1 Where NUMERIC_SCALE IS NOT NULL
Update #TableColumns SET IS_DATETIME = 1 Where DATETIME_PRECISION IS NOT NULL
Update #TableColumns SET COLUMN_DEFAULT ='' WHERE COLUMN_DEFAULT IS NULL
Update #TableColumns SET CHARACTER_MAXIMUM_LENGTH=0 WHERE CHARACTER_MAXIMUM_LENGTH is NULL
Update #TableColumns SET CHARACTER_OCTET_LENGTH=0 WHERE CHARACTER_OCTET_LENGTH IS NULL
Update #TableColumns SET NUMERIC_PRECISION =0 WHERE NUMERIC_PRECISION IS NULL
Update #TableColumns SET NUMERIC_PRECISION_RADIX = 0 WHERE NUMERIC_PRECISION_RADIX IS NULL
Update #TableColumns SET NUMERIC_SCALE = 0 WHERE NUMERIC_SCALE is NULL
Update #TableColumns SET DATETIME_PRECISION=0 WHERE DATETIME_PRECISION is NULL
PRINT 'Tables & Views that exists only in '+@DB1
print '-----------------------------------'+Replicate('-', len(@DB1))
Select Distinct Table_Schema +'.'+Table_Name
From #TableColumns
Where Table_Catalog=@DB1 and Table_Schema+Table_Name not in (
Select Table_Schema+Table_Name from #TableColumns
Where Table_Catalog =@DB2)
if @@RowCount =0
Print '(none)'
Print ''
Print 'Tables & Views that exists only in '+@DB2
print '-----------------------------------'+replicate('-', len(@DB2))
Select Distinct Table_Schema+'.'+Table_Name
From #TableColumns
Where Table_Catalog=@DB2 and Table_Schema+Table_Name not in (
Select Table_Schema+Table_Name from #TableColumns
Where Table_Catalog =@DB1)
if @@RowCount =0
Print '(none)'
Print ''
--Now check for additional Columns
Print 'Columns that are missing on the table/View'
Print '------------------------------------------'
Select Distinct [Missing in Database] =
Case A.Table_Catalog
WHEN @DB1 THEN @DB2
ELSE @DB1
END,
[Missing Column]=A.Table_Catalog+'.'+
A.Table_Schema+'.'+A.Table_Name+' - '+ A.Column_Name,
'Default'=
CASE A.IS_DEFAULT
WHEN 1 THEN A.Column_Default
ELSE NULL
END,
'Allow Null' =
CASE A.Is_Nullable
WHEN 1 THEN 'Yes'
ELSE 'NO'
END,
A.Data_Type [Data type],
'Maximum Length'=
CASE A.IS_MAXIMUM
WHEN 1 THEN A.Character_Maximum_Length
ELSE NULL
END,
'Numeric Precision'=
CASE A.IS_PRECISION
WHEN 1 THEN A.Numeric_Precision
ELSE NULL
END,
'Numeric Precision Radix'=
CASE A.IS_RADIX
WHEN 1 THEN A.Numeric_Precision_Radix
ELSE NULL
END,
'Numeric Scale'=
CASE A.IS_SCALE
WHEN 1 THEN A.Numeric_Scale
ELSE NULL
END,
'Date Time Precision'=
case A.IS_DateTime
WHEN 1 THEN A.Datetime_Precision
ELSE NULL
END,
'Identity ?'=
CASE A.IS_IDENTITY
WHEN 1 THEN 'Yes'
ELSE 'No'
END ,
Seed =
Case A.IS_IDENTITY
WHEN 1 THEN A.IDENTITY_SEED
ELSE NULL
END,
Increment =
Case A.IS_IDENTITY
WHEN 1 THEN A.IDENTITY_INCR
ELSE NULL
END
From (#TableColumns A
LEFT OUTER JOIN #TableColumns B On
A.Table_Schema= B.Table_Schema and
A.Column_Name = B.Column_Name and
A.Table_Name = B.Table_Name and A.Table_Catalog<>B.Table_Catalog)
INNER JOIN #TableColumns C On A.Table_Schema= C.Table_Schema and
A.Table_Name = C.Table_Name and
A.Table_Catalog<>C.Table_Catalog
Where B.Table_Name is NULL
Order By A.Table_Catalog+'.'+A.Table_Schema+'.'+ A.Table_Name+' - '+ A.Column_Name
IF @@ROWCOUNT =0
Print '(none)'
--Column is there but length / precision is different
Print 'Different Columns Definitions'
Print '-----------------------------'
Select A.Table_Name, A.Column_Name,
A.Table_Catalog+'.'+ A.Table_Schema as [Data Base],
'Default'=
CASE A.IS_DEFAULT
WHEN 1 THEN A.Column_Default
ELSE NULL
END,
'Allow Null' =
CASE A.Is_Nullable
WHEN 1 THEN 'Yes'
ELSE 'NO'
END,
A.Data_Type [Data type],
'Maximum Length'=
CASE A.IS_MAXIMUM
WHEN 1 THEN A.Character_Maximum_Length
ELSE NULL
END,
'Numeric Precision'=
CASE A.IS_PRECISION
WHEN 1 THEN A.Numeric_Precision
ELSE NULL
END,
'Numeric Precision Radix'=
CASE A.IS_RADIX
WHEN 1 THEN A.Numeric_Precision_Radix
ELSE NULL
END,
'Numeric Scale'=
CASE A.IS_SCALE
WHEN 1 THEN A.Numeric_Scale
ELSE NULL
END,
'Date Time Precision'=
case A.IS_DateTime
WHEN 1 THEN A.Datetime_Precision
ELSE NULL
END,
'Identity ?'=
CASE A.IS_IDENTITY
WHEN 1 THEN 'Yes'
ELSE 'No'
END ,
Seed =
Case A.IS_IDENTITY
WHEN 1 THEN A.IDENTITY_SEED
ELSE NULL
END,
increment =
Case A.IS_IDENTITY
WHEN 1 THEN A.IDENTITY_INCR
ELSE NULL
END
From #TableColumns A
INNER JOIN #TableColumns B On
A.Table_Catalog<>B.Table_Catalog and
A.Table_Schema= B.Table_Schema and
A.Table_Name = B.Table_Name and
A.Column_Name = B.Column_Name and
(A.Column_Default<>B.Column_Default or
A.Is_Nullable<>B.Is_Nullable or
A.Data_Type<>B.Data_Type or
A.Character_Maximum_Length<>B.Character_Maximum_Length or
A.Numeric_Precision<>B.Numeric_Precision or
A.Numeric_Scale<>B.Numeric_Scale or
A.Datetime_Precision<>B.Datetime_Precision or
A.IS_IDENTITY<>B.IS_IDENTITY or
A.IDENTITY_SEED<>B.IDENTITY_SEED or
A.IDENTITY_INCR<>B.IDENTITY_INCR or
A.IS_DEFAULT <>B.IS_DEFAULT or
A.IS_MAXIMUM <>B.IS_MAXIMUM or
A.IS_OCTET <>B.IS_OCTET or
A.IS_PRECISION <>B.IS_PRECISION or
A.IS_RADIX <>B.IS_RADIX or
A.IS_SCALE <>B.IS_SCALE or
A.IS_DATETIME<>B.IS_DATETIME)
Order By A.Table_Name, A.Column_Name,
A.Table_Catalog+'.'+A.Table_Schema
IF @@ROWCOUNT =0
Print '(none)'
Print ''
Print ''
----------------------------------------------------------------------------------------
-- Part II:
----------------------------------------------------------------------------------------
-- Get the other objects found only in @DB1
Set @SQL = 'Insert into #Procedures (DBName, UserName, ProcName, Type, ObjID)
Select ''' + @DB1 +''', A.Name, B.Name, B.Type, B.ID
from ['+@SvrName1+'].['+@DBName1+'].dbo.Sysusers A,
['+@SvrName1+'].['+@DBName1+'].dbo.SysObjects B
Where A.uid = B.uid and
B.Category = 0 and B.Type in (''P'', ''V'', ''Tr'', ''FN'')
and A.Name+B.Name NOT IN (Select A.Name+B.Name
From ['+@SvrName2+'].['+@DBName2+'].dbo.Sysusers A,
['++@SvrName2+'].['+@DBName2+'].dbo.SysObjects B Where A.uid = B.uid)'
Execute(@SQL)
-- Get the objects found only in @DB1
Set @SQL = 'Insert into #Procedures (DBName, UserName, ProcName, Type, ObjID)
Select ''' + @DB2 +''', A.Name, B.Name, B.Type, B.ID
from ['+@SvrName2+'].['+@DBName2+'].dbo.Sysusers A,
['+@SvrName2+'].['+@DBName2+'].dbo.SysObjects B
Where A.uid = B.uid and
B.Category = 0 and B.Type in (''P'', ''V'', ''Tr'', ''FN'')
and A.Name+B.Name NOT IN (Select A.Name+B.Name
From ['+@SvrName1+'].['+@DBName1+'].dbo.Sysusers A,
['+@SvrName1+'].['+@DBName1+'].dbo.SysObjects B
Where A.uid = B.uid)'
Execute(@SQL)
-- Get the existing Objects
Set @SQL = 'Insert into #Procedures (DBName, UserName, ProcName, Type, ObjID, ObjID2)
Select ''' + @DB1 +''', A.Name, B.Name, B.type, B.ID, D.ID
from ['+@SvrName1+'].['+@DBName1 +'].dbo.Sysusers A,
['+@SvrName1+'].['+@DBName1 +'].dbo.SysObjects B,
['+@SvrName2+'].['+@DBName2 +'].dbo.SysUsers C,
['+@SvrName2+'].['+@DBName2+'].dbo.SysObjects D
Where A.uid = B.uid and B.Category = 0 and B.Type in (''P'', ''V'', ''Tr'', ''FN'')
and A.Name = C.Name and B.Name=D.Name and C.uid = D.uid'
Execute(@SQL)
Set @SQL = 'Insert into #Procedures (DBName, UserName, ProcName, Type, ObjID, ObjID2)
Select ''' + @DB2 +''', A.Name, B.Name, B.type, B.ID, D.ID
from ['+@SvrName2+'].['+@DBName2 +'].dbo.Sysusers A,
['+@SvrName2+'].['+@DBName2 +'].dbo.SysObjects B,
['+@SvrName1+'].['+@DBName1 +'].dbo.SysUsers C,
['+@SvrName1+'].['+@DBName1+'].dbo.SysObjects D
Where A.uid = B.uid and B.Category = 0 and B.Type in (''P'', ''V'', ''Tr'', ''FN'')
and A.Name = C.Name and B.Name=D.Name and C.uid = D.uid'
Execute(@SQL)
--Get the Text of the objects
SET @SQL ='Insert into #ProcText
Select P.ProcID, C.COLID, C.Text
From #Procedures P, ['+@SvrName1+'].['+@DBName1+'].dbo.SysComments C
Where P.objID =c.id and P.DBName ='''+@DB1+''''
Execute(@SQL)
SET @SQL ='Insert into #ProcText
Select P.ProcID, C.ColID, C.Text
From #Procedures P, ['+@SvrName2+'].['+@DBName2+'].dbo.SysComments C
Where P.objID =C.id and P.DBName ='''+@DB2+''''
Execute(@SQL)
DECLARE ms_crs_syscom CURSOR LOCAL FORWARD_ONLY
FOR Select P.ProcID, D.PText from #Procedures P, #ProcText D
WHERE P.ProcID = D.PRocID Order By P.DBName, P.ProcID, D.ColID
FOR READ ONLY
SELECT @LFCR = 2
SELECT @LineId = 1
OPEN ms_crs_syscom
SET @OldProcID = -1
FETCH NEXT FROM ms_crs_syscom into @ProcID, @SyscomText
WHILE @@fetch_status = 0
BEGIN
SELECT @BasePos = 1
SELECT @CurrentPos = 1
SELECT @TextLength = LEN(@SyscomText)
IF @ProcID <>@OldProcID
BEGIN
SET @LineID = 1
SET @OldProcID = @ProcID
END
WHILE @CurrentPos != 0
BEGIN
--Looking for end of line followed by carriage return
SELECT @CurrentPos = CHARINDEX(char(13)+char(10), @SyscomText, @BasePos)
--If carriage return found
IF @CurrentPos != 0
BEGIN
/*If new value for @Lines length will be > then the
**set length then insert current contents of @line
**and proceed.
*/
While (isnull(LEN(@Line),0) + @BlankSpaceAdded + @CurrentPos-@BasePos + @LFCR) > @DefinedLength
BEGIN
SELECT @AddOnLen = @DefinedLength-(isnull(LEN(@Line),0) + @BlankSpaceAdded)
INSERT #ProcLineText VALUES
( @ProcID, @LineId,
isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText, @BasePos, @AddOnLen), N''))
SELECT @Line = NULL, @LineId = @LineId + 1,
@BasePos = @BasePos + @AddOnLen, @BlankSpaceAdded = 0
END
SELECT @Line = isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText, @BasePos, @CurrentPos-@BasePos + @LFCR), N'')
SELECT @BasePos = @CurrentPos+2
INSERT #ProcLineText VALUES( @ProcID, @LineId, @Line )
SELECT @LineId = @LineId + 1
SELECT @Line = NULL
END
ELSE
--else carriage return not found
BEGIN
IF @BasePos <= @TextLength
BEGIN
/*If new value for @Lines length will be > then the
**defined length
*/
While (isnull(LEN(@Line),0) + @BlankSpaceAdded + @TextLength-@BasePos+1 ) > @DefinedLength
BEGIN
SELECT @AddOnLen = @DefinedLength - (isnull(LEN(@Line),0) + @BlankSpaceAdded)
INSERT #ProcLineText VALUES
( @ProcID, @LineId,
isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText, @BasePos, @AddOnLen), N''))
SELECT @Line = NULL, @LineId = @LineId + 1,
@BasePos = @BasePos + @AddOnLen, @BlankSpaceAdded = 0
END
SELECT @Line = isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText, @BasePos, @TextLength-@BasePos+1 ), N'')
if LEN(@Line) < @DefinedLength and charindex(' ', @SyscomText, @TextLength+1 ) > 0
BEGIN
SELECT @Line = @Line + ' ', @BlankSpaceAdded = 1
END
END
END
END
FETCH NEXT FROM ms_crs_syscom into @ProcID, @SyscomText
END
IF @Line is NOT NULL
INSERT #ProcLineText VALUES( @ProcId, @LineId, @Line )
CLOSE ms_crs_syscom
IF (@ShowDifferentOnly=0)
BEGIN
Print 'Procedures/Triggers Found Only in ' +@DB1
Print '----------------------------------'+Replicate('-', LEN(@DB1))
Select L.LineText as 'Text'
From #ProcLineText L, #Procedures P
Where P.DBName =@DB1 and P.ObjID2 is NULL and L.ProcID = P.ProcID and
P.Type<>'V'
Order By ObjID, LineID
IF @@RowCount=0
Print '(None)'
Print ' '
Print 'Procedures/Triggers Found Only in ' +@DB2
Print '----------------------------------'+Replicate('-', LEN(@DB2))
Select L.LineText as 'Text'
From #ProcLineText L, #Procedures P
Where P.DBName =@DB2 and P.ObjID2 is NULL and P.ProcId = L.PRocID and
P.Type<>'V'
Order By ObjID, LineID
IF @@RowCount=0
Print '(None)'
Print ' '
END
ELSE
BEGIN
Print 'Procedures/Triggers Found Only in ' +@DB1
Print '----------------------------------'+Replicate('-', LEN(@DB1))
Select UserName+'.'+ ProcName
From #Procedures
Where DBName =@DB1 and ObjID2 is NULL and Type<>'V'
IF @@RowCount=0
Print '(None)'
Print ' '
Print 'Procedures/Triggers Found Only in ' +@DB2
Print '-----------------------------------'+Replicate('-', LEN(@DB2))
Select UserName+'.'+ ProcName
From #Procedures
Where DBName =@DB2 and ObjID2 is NULL and Type<>'V'
IF @@RowCount=0
Print '(None)'
Print ' '
END
Print ' '
--This part shows only the different lines
IF (@ShowDifferentOnly=1)
BEGIN
Print 'Views, Stored Procedures and Triggers with different Code (Lines Only)'
Print '---------------------------------------------------------------------'
Select P.UserName+'.'+P.ProcName [Procedure Name], P.DBName [Database Name],
A.LineID [Line ID], A.LineText [Text]
From #ProcLineText A, #ProcLineText B, #Procedures P, #Procedures Q
Where P.ProcName = Q.ProcName and P.UserName = Q.UserName and
P.ProcID=A.ProcID and Q.ProcID = B.ProcID and P.ProcID<>Q.ProcID and
A.LineID = B.LineID and
P.ObjID2 =Q.ObjID and Q.ObjID2 =P.ObjID and
A.LineText <>B.LineText
Order By P.UserName+'.'+P.ProcName, A.LineID, P.DBName
IF @@RowCount=0
Print '(None)'
END
ELSE
BEGIN
Print ''
Print ''
--This part shows all lines of the object (both databses) where there is a differnece
Print 'Views, Stored Procedures and Triggers with different Code (Complete SQL Statements)'
Print '-----------------------------------------------------------------------------------'
Print @DB1
Print Replicate('-', Len(@DB1))
Select C.LineText [Text]
from #ProcLineText C,
(Select P.ProcID
From #ProcLineText A, #ProcLineText B, #Procedures P, #Procedures Q
Where P.ProcName = Q.ProcName and P.UserName = Q.UserName and
P.ProcID=A.ProcID and Q.ProcID = B.ProcID and P.ProcID<>Q.ProcID and
A.LineID = B.LineID and P.DBName =@DB1 and Q.DBName = @DB2 and
P.ObjID2 =Q.ObjID and Q.ObjID2 =P.ObjID and
A.LineText <>B.LineText) D
Where C.ProcID =D.ProcID
Order By C.ProcID, C.LineId
IF @@RowCount=0
Print '(None)'
Print ''
Print @DB2
Print Replicate('-', Len(@DB2))
Select C.LineText [Text]
from #ProcLineText C,
(Select P.ProcID
From #ProcLineText A, #ProcLineText B, #Procedures P, #Procedures Q
Where P.ProcName = Q.ProcName and P.UserName = Q.UserName and
P.ProcID=A.ProcID and Q.ProcID = B.ProcID and P.ProcID<>Q.ProcID and
A.LineID = B.LineID and P.DBName =@DB2 and Q.DBName = @DB1 and
P.ObjID2 =Q.ObjID and Q.ObjID2 =P.ObjID and
A.LineText <>B.LineText) D
Where C.ProcID =D.ProcID
Order By C.ProcID, C.LineId
IF @@RowCount=0
Print '(None)'
END
Print '-------------------------- End ---------------------------------------'
Deallocate ms_crs_syscom
Drop Table #ProcLineText
Drop Table #ProcText
Drop table #Procedures
Drop table #TableColumns
Set nocount off
