This SP compare betwwen 2 objects content in the DB.
Object can be Stored procedure, function, view or trigger.
The main output is whether the objects are the same, if not what percentage of the differences and what are the differences and whether calls to the object to SP or other functions.
Can be used to compare objects, finding differences between two versions or two objects, identifying errors, etc..
Output example:
SP code:
CREATE PROCEDURE [dbo].[Itai_CompareObjectsContent]
@ObjName1 NVARCHAR(500),
@Schema1 NVARCHAR(50) = 'dbo',
@ObjName2 NVARCHAR(500),
@Schema2 NVARCHAR(50) = 'dbo',
@ShowSelect int = 0, -- show the objects content
@ShowDiffSelect int = 1 -- show the differences betwwen the objects
AS
SET NOCOUNT ON
DECLARE @Obj1 nvarchar(max)
SET @Obj1 = @Schema1 + N'.' + @ObjName1
DECLARE @Obj2 nvarchar(max)
SET @Obj2 = @Schema2 + N'.' + @ObjName2
-------------------------------------------------------------------
-- insert lines of object #1 into temp table:
CREATE TABLE #obj1 (
CodeLineTxt nvarchar(max),
CodeLineNum int not null identity(1,1)
)
INSERT INTO #obj1 (CodeLineTxt)
EXEC sp_HelpText @Obj1
-- insert lines of object #2 into temp table:
CREATE TABLE #obj2 (
CodeLineTxt nvarchar(max),
CodeLineNum int not null identity(1,1)
)
INSERT INTO #obj2 (CodeLineTxt)
EXEC sp_HelpText @Obj2
-- show objects content
IF @ShowSelect = 1
BEGIN
select CodeLineTxt, CodeLineNum from #obj1
select CodeLineTxt, CodeLineNum from #obj2
END
-------------------------------------------------------------------
-- replace spaces and empty lines
-- in order not to get differences of them
--REPLACE spaces
UPDATE #obj1 SET CodeLineTxt = REPLACE(CodeLineTxt, N' ', N'')
UPDATE #obj2 SET CodeLineTxt = REPLACE(CodeLineTxt, N' ', N'')
--REPLACE char(9) (horizontal tab)
UPDATE #obj1 SET CodeLineTxt = REPLACE(CodeLineTxt, char(9), N'')
UPDATE #obj2 SET CodeLineTxt = REPLACE(CodeLineTxt, char(9), N'')
--REPLACE char(10) (new line)
UPDATE #obj1 SET CodeLineTxt = REPLACE(CodeLineTxt, char(10), N'')
UPDATE #obj2 SET CodeLineTxt = REPLACE(CodeLineTxt, char(10), N'')
--REPLACE char(13) (carriage return)
UPDATE #obj1 SET CodeLineTxt = REPLACE(CodeLineTxt, char(13), N'')
UPDATE #obj2 SET CodeLineTxt = REPLACE(CodeLineTxt, char(13), N'')
--REPLACE char(32) (space)
UPDATE #obj1 SET CodeLineTxt = REPLACE(CodeLineTxt, char(32), N'')
UPDATE #obj2 SET CodeLineTxt = REPLACE(CodeLineTxt, char(32), N'')
-- delete empty rows
DELETE FROM #obj1 WHERE CodeLineTxt = N''
DELETE FROM #obj2 WHERE CodeLineTxt = N''
-------------------------------------------------------------------
-- replace schemas to dbo
-- in order not to get differences of schemas names
if @Schema1 <> 'dbo'
UPDATE #obj1 SET CodeLineTxt = REPLACE(CodeLineTxt, '[' + @Schema1 + ']', '[dbo]')
if @Schema2 <> 'dbo'
UPDATE #obj2 SET CodeLineTxt = REPLACE(CodeLineTxt, '[' + @Schema2 + ']', '[dbo]')
-------------------------------------------------------------------
-- raw count of #lines in object #1:
DECLARE @cnt1 int
SELECT @cnt1 = COUNT(*) FROM #obj1
-- count of #lines in object #1 that match in object #2:
DECLARE @cnt1a int
SELECT @cnt1a = COUNT(*)
FROM #obj1 T1
WHERE EXISTS (SELECT NULL FROM #obj2 T2 WHERE T2.CodeLineTxt = T1.CodeLineTxt)
-- raw count of #lines in object #2:
DECLARE @cnt2 int
SELECT @cnt2 = COUNT(*) FROM #obj2
-- count of #lines in object #2 that match in object #1:
DECLARE @cnt2a int
SELECT @cnt2a = COUNT(*)
FROM #obj2 T1
WHERE EXISTS (SELECT NULL FROM #obj1 T2 WHERE T2.CodeLineTxt = T1.CodeLineTxt)
-------------------------------------------------------------------
-- show differences betwwen the objects
IF @ShowDiffSelect = 1
BEGIN
SELECT tt1.ROW_NUMBER_ as ROW_NUM,
tt1.CodeLineNum as obj1_CodeLineNum,
tt1.CodeLineTxt as obj1_CodeLineTxt,
tt2.CodeLineTxt as obj2_CodeLineTxt,
tt2.CodeLineNum as obj2_CodeLineNum
FROM ( SELECT CodeLineTxt , CodeLineNum, ROW_NUMBER_
FROM (select *, ROW_NUMBER() OVER (ORDER BY CodeLineNum) as ROW_NUMBER_ from #obj1) T1
WHERE NOT EXISTS (SELECT NULL FROM #obj2 T2 WHERE T2.CodeLineTxt = T1.CodeLineTxt)
) tt1
FULL OUTER JOIN
( SELECT CodeLineTxt , CodeLineNum, ROW_NUMBER_
FROM (select *, ROW_NUMBER() OVER (ORDER BY CodeLineNum) as ROW_NUMBER_ from #obj2) T3
WHERE NOT EXISTS (SELECT NULL FROM #obj1 T4 WHERE T4.CodeLineTxt = T3.CodeLineTxt)
) tt2
ON tt1.ROW_NUMBER_ = tt2.ROW_NUMBER_
ORDER BY isnull(tt1.ROW_NUMBER_,isnull(tt1.CodeLineNum,tt2.CodeLineNum))
END
DECLARE @CallsCount int
PRINT 'object #1: ' + @Obj1
PRINT 'object #2: ' + @Obj2
PRINT ''
SELECT @CallsCount = COUNT(1) FROM #obj1
PRINT 'Rows number, object #1: ' + cast(@CallsCount as nvarchar(50))
SELECT @CallsCount = COUNT(1) FROM #obj2
PRINT 'Rows number, object #2: ' + cast(@CallsCount as nvarchar(50))
PRINT ''
PRINT 'Percentage match between the objects: '
+ LTRIM(STR(100.0 * (@cnt1a + @cnt2a) / (1.0 * @cnt1 + @cnt2), 10, 2)) + '%'
PRINT 'Percentage of object #1 found in object #2: '
+ LTRIM(STR(100.0 * @cnt1a / (1.0 * @cnt1), 10, 2)) + '%'
PRINT 'Percentage of object #2 found in object #1: '
+ LTRIM(STR(100.0 * @cnt2a / (1.0 * @cnt2), 10, 2)) + '%'
PRINT ''
SELECT @CallsCount = COUNT(1) FROM #obj1 where CodeLineTxt like '%EXEC%'
Print 'Calls to SPs from object #1: ' + cast(@CallsCount as nvarchar(10))
SELECT @CallsCount = COUNT(1) FROM #obj2 where CodeLineTxt like '%EXEC%'
Print 'Calls to SPs from object #2: ' + cast(@CallsCount as nvarchar(10))
declare @LikeSchema nvarchar(50), @LikeSchemaOr nvarchar(50)
set @LikeSchema = '%dbo.%'
set @LikeSchemaOr = '%[[]dbo].%'
SELECT @CallsCount = COUNT(1) FROM #obj1 where CodeLineTxt like @LikeSchema or CodeLineTxt like @LikeSchemaOr
SET @CallsCount -= 1 -- don't count CREATE ... [dbo].....
Print 'Calls to Functions from object #1: ' + cast(@CallsCount as nvarchar(10))
SELECT @CallsCount = COUNT(1) FROM #obj2 where CodeLineTxt like @LikeSchema or CodeLineTxt like @LikeSchemaOr
SET @CallsCount -= 1 -- don't count CREATE ... [dbo].....
Print 'Calls to Functions from object #2: ' + cast(@CallsCount as nvarchar(10))
PRINT ''
DROP TABLE #obj1
DROP TABLE #obj2
SET NOCOUNT OFF
GO
No comments:
Post a Comment