USE master
GO
IF OBJECT_ID('sp_Compare') IS NULL EXEC ('CREATE PROCEDURE sp_Compare AS SELECT 1 AS ID')
GO
ALTER PROCEDURE dbo.sp_Compare
@TableLeft VARCHAR(256) = NULL,
@WhereClauseLeft VARCHAR(MAX) = NULL,
@TableRight VARCHAR(256) = NULL,
@WhereClauseRight VARCHAR(MAX) = NULL,
@KeyColumnList VARCHAR(MAX) = NULL,
@IncludeColumnList VARCHAR(MAX) = NULL,
@ExcludeColumnList VARCHAR(MAX) = NULL,
@ColumnDelimiter VARCHAR(30) = ',',
@ResultToColumnValuePair BIT = 1,
@CreateUnpivotView BIT = 1,
@SortColumnsAlpha BIT = 1,
@MaxColumnsInARow INT = 10,
@AllColumnsNullable BIT = 0,
@PrintSQL BIT = 1,
@Recipients NVARCHAR(1000) = NULL,
@SQL VARCHAR(MAX) = NULL OUTPUT,
@DataCompression VARCHAR(100) = 'PAGE',
@ResultTableName VARCHAR(256) = NULL,
@FilePath VARCHAR(4000) = NULL /* Will write the generated sql to the path */
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET ANSI_WARNINGS OFF
SELECT @ResultToColumnValuePair = ISNULL(@ResultToColumnValuePair, 0),
@SortColumnsAlpha = ISNULL(@SortColumnsAlpha, 1),
@MaxColumnsInARow = ISNULL(@MaxColumnsInARow, 40),
@AllColumnsNullable = ISNULL(@AllColumnsNullable, 0),
@PrintSQL = ISNULL(@PrintSQL, 1),
@DataCompression = CASE WHEN @DataCompression IN ('ROW', 'PAGE') THEN ' WITH (DATA_COMPRESSION = ' + @DataCompression + ')'
ELSE ''
END
SET @MaxColumnsInARow = CASE WHEN ISNULL(@MaxColumnsInARow, 0) = 0 THEN 1
ELSE @MaxColumnsInARow
END
DECLARE @MaxCompareInARow INT = CASE WHEN @MaxColumnsInARow / 10 > 0 THEN @MaxColumnsInARow / 10
ELSE 1
END
DECLARE @LeftObjectID INT = OBJECT_ID(Util.dbo.TrimBothEnds(@TableLeft)),
@RightObjectID INT = OBJECT_ID(Util.dbo.TrimBothEnds(@TableRight)),
@LeftDatabase VARCHAR(MAX) = ISNULL(PARSENAME(@TableLeft, 3), DB_NAME()),
@RightDatabase VARCHAR(MAX) = ISNULL(PARSENAME(@TableRight, 3), DB_NAME()),
@TablePostFix VARCHAR(MAX) = '_' + CONVERT(VARCHAR(100), GETDATE(), 112) + '_' + REPLACE(CONVERT(VARCHAR(100), GETDATE(), 108), ':', '') + '_'
+ RIGHT(REPLACE(CAST(NEWID() AS VARCHAR(128)), '-', ''), 8),
@RegionStart VARCHAR(MAX) = '--' + '#' + 'region ',
@RegionEnd VARCHAR(MAX) = '--' + '#' + 'endregion ' ;
IF @LeftObjectID IS NULL
OR @RightObjectID IS NULL
-- OR @LeftObjectID = @RightObjectID
BEGIN
PRINT 'DECLARE @SQL VARCHAR(MAX)
EXEC sp_Compare
@ResultToColumnValuePair = 0, -- It will generate output in column value pair
@CreateUnpivotView = 1, -- If regular result created it will create a view to Unpivot the data
@TableLeft = NULL, -- The table name or sql statement that will host the left data in comparison
@WhereClauseLeft = NULL, -- starts with "WHERE", to filter left table, no aliases
@TableRight = NULL, -- The table name or sql statement that will host the right data in comparison
@WhereClauseRight = NULL, -- starts with "WHERE", to filter right table, no aliases
@KeyColumnList = NULL, -- List of key columns, not needed if the table has a primary key or unique index
@IncludeColumnList = NULL, -- The non key columns to include
@ExcludeColumnList = NULL, -- The non key columns to exclude
@ColumnDelimiter = '','', -- Column delimiter for above lists
@SortColumnsAlpha = 1, -- Sorts columns alphabetically versus the order in the table
@AllColumnsNullable = 0, -- Compares all columns as if they were nullable
@ResultTableName = NULL, -- Provide a table name for the results
@PrintSQL = 1, -- Printing in message section, can be enabled or disabled
@MaxColumnsInARow = 40, -- How many columns will be printed in a new line, comparison columns will be printed less than these number since they are longer
@Recipients = NULL, -- Email the script to recipient
@DataCompression = ''PAGE'', -- Compress result tables
@FilePath = NULL -- Since some content may be more than 8000 bytes per line and it won''t print as a message (and xml will convert <> to <>) a file name can be provided for output, don''t forget it will be written from sql server
-- @FilePath = ''C:\Temp\Out.sql'' -- therefore a local path will be local to the sql server
'
EXEC master.dbo.sp_ExecTemplate
@ObjectName = 'sp_Compare',
@PrintDefault = 0,
@DeclareReturn = 0,
@PrintReturnLine = 0,
@DoNotDeclareVar = 1
RETURN
END
IF OBJECT_ID('tempdb..#tempList') IS NOT NULL
DROP TABLE #tempList
CREATE TABLE #tempList (ColumnName VARCHAR(256) NOT NULL
PRIMARY KEY CLUSTERED)
INSERT #tempList
(ColumnName)
SELECT DISTINCT
Util.dbo.TrimBothEnds(PARSENAME(Field, 1)) AS ColumnName
FROM Util.dbo.ParseDelimited(@IncludeColumnList, ISNULL(NULLIF(@ColumnDelimiter, ''), ','))
WHERE Field <> ''
DECLARE @TempCount INT = @@ROWCOUNT
IF OBJECT_ID('tempdb..#ExcludeList') IS NOT NULL
DROP TABLE #ExcludeList
CREATE TABLE #ExcludeList (ColumnName VARCHAR(256) NOT NULL
PRIMARY KEY CLUSTERED)
INSERT #ExcludeList
(ColumnName)
SELECT Util.dbo.TrimBothEnds(PARSENAME(Field, 1)) AS ColumnName
FROM Util.dbo.ParseDelimited(@ExcludeColumnList, ISNULL(NULLIF(@ColumnDelimiter, ''), ','))
WHERE Field <> ''
DECLARE @ExcludeCount INT = @@ROWCOUNT
IF OBJECT_ID('tempdb..#KeyList') IS NOT NULL
DROP TABLE #KeyList
CREATE TABLE #KeyList (ColumnName VARCHAR(256) NOT NULL
PRIMARY KEY CLUSTERED,
key_ordinal SMALLINT NOT NULL
UNIQUE NONCLUSTERED)
INSERT #KeyList
(ColumnName,
key_ordinal)
SELECT Util.dbo.TrimBothEnds(PARSENAME(Field, 1)) AS ColumnName,
FieldNum
FROM Util.dbo.ParseDelimited(@KeyColumnList, ISNULL(NULLIF(@ColumnDelimiter, ''), ','))
WHERE Field <> ''
DECLARE @KeyCount INT = @@ROWCOUNT
DECLARE @TempTableName VARCHAR(256) = ISNULL(@ResultTableName, '##Compare' + @TablePostFix)
IF OBJECT_ID('tempdb..#Cols') IS NOT NULL
DROP TABLE #Cols
CREATE TABLE #Cols ([KeyColumn] VARCHAR(128) NULL,
[NonKeyColumn] VARCHAR(128) NULL,
[ColumnName] VARCHAR(128) NOT NULL,
[is_nullable] INT NOT NULL,
[TypeName] SYSNAME NOT NULL,
[NewLine] VARCHAR(3) NOT NULL,
[CompNewLine] VARCHAR(3) NOT NULL,
[column_id] INT NOT NULL,
Sort1 INT NOT NULL,
Sort2 VARCHAR(256) NOT NULL PRIMARY KEY CLUSTERED (Sort1, Sort2, column_id))
DECLARE @TempSQL NVARCHAR(MAX)= '
SELECT @TableLeft = (SELECT QUOTENAME(@LeftDatabase) + ''.'' + QUOTENAME(s.name) + ''.'' + QUOTENAME(t.NAME) FROM ' + QUOTENAME(@LeftDatabase)
+ '.sys.schemas s (NOLOCK) INNER JOIN ' + QUOTENAME(@LeftDatabase) + '.sys.objects t (NOLOCK) ON s.schema_id = t.schema_id AND t.object_id = @LeftObjectID),
@TableRight = (SELECT QUOTENAME(@RightDatabase) + ''.'' + QUOTENAME(s.name) + ''.'' + QUOTENAME(t.NAME) FROM ' + QUOTENAME(@RightDatabase)
+ '.sys.schemas s (NOLOCK) INNER JOIN ' + QUOTENAME(@RightDatabase)
+ '.sys.objects t (NOLOCK) ON s.schema_id = t.schema_id AND t.object_id = @RightObjectID)
SELECT l.name AS ColumnName
INTO #IncludeList
FROM ' + @LeftDatabase + '.sys.columns l (NOLOCK)
INNER JOIN ' + @RightDatabase + '.sys.columns r (NOLOCK) ON l.name = r.name
WHERE l.object_id = @LeftObjectID
AND r.object_id = @RightObjectId
AND (@TempCount = 0
OR EXISTS ( SELECT *
FROM #tempList
WHERE ColumnName = l.name ))
DECLARE @IncludeCount INT = @@ROWCOUNT
INSERT #Cols WITH (TABLOCK)
([KeyColumn],
[NonKeyColumn],
[ColumnName],
[is_nullable],
[TypeName],
[NewLine],
[CompNewLine],
[column_id],
Sort1,
Sort2)
SELECT CASE WHEN ISNULL(ik.key_ordinal, ic.key_ordinal) IS NOT NULL THEN c.NAME
END AS KeyColumn,
CASE WHEN ISNULL(ik.key_ordinal, ic.key_ordinal) IS NULL THEN c.NAME
END AS NonKeyColumn,
c.NAME AS ColumnName,
CASE WHEN @AllColumnsNullable = 1 THEN 1
ELSE c.is_nullable
END AS is_nullable,
ty.name AS TypeName,
NewLine,
CompNewLine,
c.column_id,
ISNULL(ic.key_ordinal, 255) AS Sort1,
CASE WHEN @SortColumnsAlpha = 1 THEN c.NAME
ELSE ''''
END AS Sort2
FROM ' + @LeftDatabase + '.sys.schemas s (NOLOCK)
INNER JOIN ' + @LeftDatabase + '.sys.tables t (NOLOCK) ON t.schema_id = s.schema_id
INNER JOIN ' + @LeftDatabase + '.sys.columns c (NOLOCK) ON c.object_id = t.object_id
INNER JOIN ' + @LeftDatabase + '.sys.types ty (NOLOCK) ON ty.user_type_id = c.user_type_id
CROSS APPLY (SELECT CASE WHEN @MaxColumnsInARow = 1
OR (@MaxColumnsInARow > 1
AND c.column_id % @MaxColumnsInARow = 1) THEN ''
'' ELSE ''''
END AS NewLine,
CASE WHEN @MaxCompareInARow = 1
OR (@MaxCompareInARow > 1
AND c.column_id % @MaxCompareInARow = 1) THEN ''
'' ELSE ''''
END AS CompNewLine) nl
LEFT OUTER JOIN #KeyList ik ON ik.ColumnName = c.name
OUTER APPLY (SELECT TOP 1
i.index_id
FROM ' + @LeftDatabase + '.sys.indexes i (NOLOCK)
WHERE @KeyCount = 0
AND i.object_id = t.object_id
AND (i.index_id = 0
OR (i.is_primary_key = 1
OR i.is_unique_constraint = 1
OR i.is_unique = 1))
ORDER BY CASE WHEN i.index_id = 0
AND (i.is_primary_key = 1
OR i.is_unique_constraint = 1
OR i.is_unique = 1) THEN 0
WHEN i.index_id = 0 THEN 2
ELSE 3
END) i
LEFT OUTER JOIN ' + @LeftDatabase + '.sys.index_columns ic (NOLOCK) ON @KeyCount = 0
AND ic.OBJECT_ID = T.OBJECT_ID
AND ic.index_id = i.index_id
AND ic.column_id = c.column_id
WHERE t.object_id = @LeftObjectID
AND (ic.key_ordinal IS NOT NULL
OR (ic.key_ordinal IS NULL
AND (@IncludeCount = 0
OR EXISTS ( SELECT *
FROM #IncludeList
WHERE ColumnName = c.NAME ))
AND (@ExcludeCount = 0
OR NOT EXISTS ( SELECT *
FROM #ExcludeList
WHERE ColumnName = c.NAME ))))
OPTION (RECOMPILE)'
EXEC sp_executesql
@TempSQL,
N'@LeftDatabase varchar(256), @RightDatabase varchar(256), @TableLeft varchar(256) OUTPUT, @TableRight varchar(256) OUTPUT, @LeftObjectId int, @RightObjectId int, @TempCount int, @AllColumnsNullable bit, @SortColumnsAlpha bit, @MaxColumnsInARow smallint, @KeyCount int, @ExcludeCount int, @MaxCompareInARow int',
@LeftDatabase,
@RightDatabase,
@TableLeft OUTPUT,
@TableRight OUTPUT,
@LeftObjectId,
@RightObjectId,
@TempCount,
@AllColumnsNullable,
@SortColumnsAlpha,
@MaxColumnsInARow,
@KeyCount,
@ExcludeCount,
@MaxCompareInARow
IF @ResultToColumnValuePair = 0
BEGIN
DECLARE @ViewName VARCHAR(256) = ISNULL(QUOTENAME(PARSENAME(@TempTableName, 2)) + '.', '') + QUOTENAME('v' + PARSENAME(@TempTableName, 1))
SELECT @SQL = '/* WHATS HAPPENING */
-- 1-) Compare data and insert the results into a table
-- 2-) Create a unique clustered index on the table
-- 3-) Create a view or if option not selected print the select statement that will select column value pair from the result table if needed later
-- 4-) Select results
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
GO
IF OBJECT_ID(''' + CASE WHEN @TempTableName LIKE '#%' THEN 'tempdb..' + @TempTableName
ELSE @TempTableName
END + ''') IS NOT NULL DROP TABLE ' + @TempTableName + '
GO
' + @RegionStart
+ 'CREATE COMPARISON TABLE
SELECT
DiffType, DiffColumns, CASE WHEN DiffType = ''DIFF'' THEN ColumnsUpdated END AS ColumnsUpdated, CASE WHEN DiffType = ''DIFF'' THEN DiffCount END AS DiffCount,
' + Util.dbo.StringConcat('ISNULL(l.' + QUOTENAME(KeyColumn) + ', r.' + QUOTENAME(KeyColumn) + ') as ' + QUOTENAME(KeyColumn) + '', ', ') + ',
' + Util.dbo.StringConcat(NewLine + 'l.' + QUOTENAME(NonKeyColumn) + ' as ' + QUOTENAME('L-' + NonKeyColumn) + ', r.' + QUOTENAME(NonKeyColumn) + ' as '
+ QUOTENAME('R-' + NonKeyColumn) + '', ', ') + '
INTO ' + @TempTableName + '
FROM (SELECT 1 AS [MARKER$COLUMN], * FROM ' + @TableLeft + ' (NOLOCK)' + ISNULL('
' + @WhereClauseLeft, '') + ') l
FULL OUTER JOIN (SELECT 1 AS [MARKER$COLUMN], * FROM ' + @TableRight + ' (NOLOCK)' + ISNULL('
' + @WhereClauseRight, '') + ') r ON ' + Util.dbo.StringConcat(CompNewLine + CASE WHEN is_nullable = 1
THEN '((l.' + QUOTENAME(KeyColumn) + ' IS NULL AND r.' + QUOTENAME(KeyColumn)
+ ' IS NULL) OR l.' + QUOTENAME(KeyColumn) + ' = r.'
+ QUOTENAME(KeyColumn) + ')'
ELSE 'l.' + QUOTENAME(KeyColumn) + ' = r.' + QUOTENAME(KeyColumn)
END, ' AND ') + '
CROSS APPLY(SELECT Util.dbo.BuildColumnsUpdated(ColumnId) as ColumnsUpdated, Util.dbo.StringConcat(ColumnName, '', '') as DiffColumns, COUNT(*) AS DiffCount
FROM (VALUES' + Util.dbo.StringConcat('(' + CAST(column_id AS VARCHAR) + ', ''' + QUOTENAME(REPLACE(NonKeyColumn, '''', '''''')) + ''',CASE WHEN (l.'
+ QUOTENAME(NonKeyColumn) + ' IS NULL AND r.' + QUOTENAME(NonKeyColumn) + ' IS NOT NULL)
OR (l.' + QUOTENAME(NonKeyColumn) + ' IS NOT NULL AND r.' + QUOTENAME(NonKeyColumn) + ' IS NULL)
OR l.' + QUOTENAME(NonKeyColumn) + ' <> r.' + QUOTENAME(NonKeyColumn) + ' THEN 1 ELSE 0 END)', ',
')
+ ')
d (ColumnId, ColumnName, IsDifferent)
WHERE IsDifferent = 1
AND l.[MARKER$COLUMN] IS NOT NULL
AND r.[MARKER$COLUMN] IS NOT NULL)dcl
CROSS APPLY(SELECT CASE WHEN DiffCount > 0 THEN ''DIFF'' WHEN l.[MARKER$COLUMN] IS NOT NULL AND r.[MARKER$COLUMN] IS NULL THEN ''RMISS'' WHEN r.[MARKER$COLUMN] IS NOT NULL AND l.[MARKER$COLUMN] IS NULL THEN ''LMISS'' END as DiffType)dt
WHERE DiffType IS NOT NULL
' + @RegionEnd + 'CREATE COMPARISON TABLE
GO
CREATE UNIQUE CLUSTERED INDEX DiffIndex on ' + @TempTableName + '(' + Util.dbo.StringConcat(QUOTENAME(KeyColumn), ', ') + ')' + @DataCompression + '
GO
' + @RegionStart + 'CREATE VIEW OR PRINT SELECT STATEMENT TO RETRIEVE COLUMN VALUE PAIR
' + CASE WHEN @CreateUnpivotView = 1
AND @TempTableName NOT LIKE '#%' THEN ISNULL('USE ' + QUOTENAME(PARSENAME(@TempTableName, 3)) + '
GO
', '') + 'IF OBJECT_ID(''' + @ViewName + ''') IS NOT NULL DROP VIEW ' + @ViewName + '
GO
CREATE VIEW ' + @ViewName + '
AS
' ELSE 'RETURN
'
END + 'SELECT ' + Util.dbo.StringConcat(QUOTENAME(KeyColumn), ', ') + ', a.[DiffType], ColumnName, LeftValue, RightValue
FROM ' + @TempTableName + ' a (NOLOCK)
OUTER APPLY (SELECT
CASE ColumnPosition
' + Util.dbo.StringConcat(' WHEN ' + CAST(column_id AS VARCHAR) + ' THEN ''' + REPLACE(NonKeyColumn, '''', '''''') + '''', '
') + '
END AS ColumnName,
CASE ColumnPosition
' + Util.dbo.StringConcat(' WHEN ' + CAST(column_id AS VARCHAR) + ' THEN ' + CASE WHEN TypeName LIKE '%CHAR%' THEN '[L-' + NonKeyColumn + ']'
WHEN TypeName LIKE '%binary%'
THEN 'CONVERT(VARCHAR(MAX), [L-' + NonKeyColumn + '], 1)'
ELSE 'CAST([L-' + NonKeyColumn + '] AS VARCHAR)'
END, '
') + '
END AS LeftValue,
CASE ColumnPosition
' + Util.dbo.StringConcat(' WHEN ' + CAST(column_id AS VARCHAR) + ' THEN ' + CASE WHEN TypeName LIKE '%CHAR%' THEN '[R-' + NonKeyColumn + ']'
WHEN TypeName LIKE '%binary%'
THEN 'CONVERT(VARCHAR(MAX), [R-' + NonKeyColumn + '], 1)'
ELSE 'CAST([R-' + NonKeyColumn + '] AS VARCHAR)'
END, '
') + '
END AS RightValue
FROM Util.dbo.ParseColumnsUpdated (ColumnsUpdated)
WHERE ColumnsUpdated IS NOT NULL) pm
' + @RegionEnd + 'CREATE VIEW OR PRINT SELECT STATEMENT TO RETRIEVE COLUMN VALUE PAIR
GO
SELECT DiffType, DiffColumns, ColumnsUpdated, DiffCount,
' + Util.dbo.StringConcat(QUOTENAME(KeyColumn), ', ') + ',
' + Util.dbo.StringConcat(CompNewLine + QUOTENAME('L-' + NonKeyColumn) + ', ' + QUOTENAME('R-' + NonKeyColumn) + '', ', ') + '
'
+ '-- ,CASE WHEN [DifferentColumnsList] <> '''' THEN (SELECT 1 AS tag, NULL AS parent, CHAR (13) + [DifferentColumnsList] + CHAR (13) AS [Q!1!Q!CDATA] FOR XML EXPLICIT, TYPE) END AS [DifferentColumnsListXML]
FROM ' + @TempTableName
FROM #Cols
END
ELSE
IF @ResultToColumnValuePair = 1
BEGIN
SELECT @SQL = '/* WHATS HAPPENING */
-- 1-) Compare data and insert the results into a table
-- 2-) Create a unique clustered index on the table
-- 3-) Select results
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
GO
IF OBJECT_ID(''' + CASE WHEN @TempTableName LIKE '#%' THEN 'tempdb..' + @TempTableName
ELSE @TempTableName
END + ''') IS NOT NULL DROP TABLE ' + @TempTableName + '
GO
' + @RegionStart + 'CREATE COMPARISON TABLE
SELECT
' + Util.dbo.StringConcat('ISNULL(l.' + QUOTENAME(KeyColumn) + ', r.' + QUOTENAME(KeyColumn) + ') as ' + QUOTENAME(KeyColumn) + '', ', ') + ',
DiffType, ColumnName, LeftValue, RightValue
INTO ' + @TempTableName + '
FROM (SELECT 1 AS [MARKER$COLUMN], * FROM ' + @TableLeft + ' (NOLOCK)' + ISNULL('
' + @WhereClauseLeft, '') + ') l
FULL OUTER JOIN (SELECT 1 AS [MARKER$COLUMN], * FROM ' + @TableRight + ' (NOLOCK)' + ISNULL('
' + @WhereClauseRight, '') + ') r ON ' + Util.dbo.StringConcat(CompNewLine + CASE WHEN is_nullable = 1
THEN '((l.' + QUOTENAME(KeyColumn) + ' IS NULL AND r.' + QUOTENAME(KeyColumn)
+ ' IS NULL) OR l.' + QUOTENAME(KeyColumn) + ' = r.'
+ QUOTENAME(KeyColumn) + ')'
ELSE 'l.' + QUOTENAME(KeyColumn) + ' = r.' + QUOTENAME(KeyColumn)
END, ' AND ') + '
CROSS APPLY(SELECT ''DIFF'' as DiffType, ColumnName, LeftValue, RightValue
FROM (VALUES' + Util.dbo.StringConcat('(''' + REPLACE(NonKeyColumn, '''', '''''') + ''', ' + CASE WHEN TypeName LIKE '%CHAR%'
THEN 'l.' + QUOTENAME(NonKeyColumn) + ', r.'
+ QUOTENAME(NonKeyColumn)
WHEN TypeName LIKE '%binary%'
THEN 'CONVERT(VARCHAR(MAX), l.' + QUOTENAME(NonKeyColumn)
+ ', 1), CONVERT(VARCHAR(MAX), r.'
+ QUOTENAME(NonKeyColumn) + ', 1)'
ELSE 'CAST(l.' + QUOTENAME(NonKeyColumn)
+ ' AS VARCHAR), CAST(r.' + QUOTENAME(NonKeyColumn)
+ ' AS VARCHAR)'
END + ', CASE WHEN (l.' + QUOTENAME(NonKeyColumn)
+ ' IS NULL AND r.' + QUOTENAME(NonKeyColumn) + ' IS NOT NULL)
OR (l.' + QUOTENAME(NonKeyColumn) + ' IS NOT NULL AND r.' + QUOTENAME(NonKeyColumn) + ' IS NULL)
OR (l.' + QUOTENAME(NonKeyColumn) + ' <> r.' + QUOTENAME(NonKeyColumn) + ') THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT) END)', ',
') + ') as d(ColumnName, LeftValue, RightValue, IsDifferent)
WHERE l.[MARKER$COLUMN] IS NOT NULL
AND r.[MARKER$COLUMN] IS NOT NULL
AND d.IsDifferent = 1
UNION ALL
SELECT ''LMISS'' AS DiffType, NULL AS ColumnName, NULL AS LeftValue, NULL AS RightValue
WHERE l.[MARKER$COLUMN] IS NULL
UNION ALL
SELECT ''RMISS'' as DiffType, NULL AS ColumnName, NULL AS LeftValue, NULL AS RightValue
WHERE r.[MARKER$COLUMN] IS NULL) c
' + @RegionEnd + 'CREATE COMPARISON TABLE
CREATE UNIQUE CLUSTERED INDEX DiffIndex on ' + @TempTableName + '(' + Util.dbo.StringConcat(QUOTENAME(KeyColumn), ', ') + ', ColumnName)' + @DataCompression + '
SELECT DiffType,
' + Util.dbo.StringConcat(QUOTENAME(KeyColumn), ', ') + ', ColumnName, LeftValue, RightValue
FROM ' + @TempTableName
FROM #Cols
END
IF @Recipients <> ''
BEGIN
DECLARE @body NVARCHAR(MAX) = 'EXEC sp_Compare
@ResultToColumnValuePair = ' + CAST(@ResultToColumnValuePair AS VARCHAR) + ',
@TableLeft = ' + ISNULL('''' + @TableLeft + '''', 'NULL') + ' ,
@WhereClauseLeft = ' + ISNULL('''' + REPLACE(@WhereClauseLeft, '''', '''''') + '''', 'NULL') + ' ,
@TableRight = ' + ISNULL('''' + REPLACE(@TableRight, '''', '''''') + '''', 'NULL') + ' ,
@WhereClauseRight = ' + ISNULL('''' + REPLACE(@WhereClauseRight, '''', '''''') + '''', 'NULL') + ' ,
@KeyColumnList = ' + ISNULL('''' + REPLACE(@KeyColumnList, '''', '''''') + '''', 'NULL') + ' ,
@IncludeColumnList = ' + ISNULL('''' + REPLACE(@IncludeColumnList, '''', '''''') + '''', 'NULL') + ' ,
@ExcludeColumnList = ' + ISNULL('''' + REPLACE(@ExcludeColumnList, '''', '''''') + '''', 'NULL') + ' ,
@ColumnDelimiter = ' + ISNULL('''' + REPLACE(@ColumnDelimiter, '''', '''''') + '''', 'NULL') + ' ,
@AllColumnsNullable = ' + CAST(@AllColumnsNullable AS VARCHAR) + ',
@CreateUnpivotView = ' + CAST(@CreateUnpivotView AS VARCHAR) + ',
@ResultTableName = ' + ISNULL('''' + @ResultTableName + '''', 'NULL') + ' ,
@SortColumnsAlpha = ' + CAST(@SortColumnsAlpha AS VARCHAR) + ',
@MaxColumnsInARow = ' + CAST(@MaxColumnsInARow AS VARCHAR) + ',
@PrintSQL = ' + CAST(@PrintSQL AS VARCHAR) + ',
@Recipients = ' + ISNULL('''' + REPLACE(@Recipients, '''', '''''') + '''', 'NULL') + ' ,
@FilePath = ' + ISNULL('''' + REPLACE(@FilePath, '''', '''''') + '''', 'NULL') + '
' + @SQL
EXEC msdb.dbo.sp_send_dbmail
@Recipients = @Recipients,
@subject = 'sp_Compare',
@body = @body
END
IF @PrintSQL = 1
EXEC Util.dbo.PrintLargeText
@SQL
IF @FilePath <> ''
SELECT ReturnVal,
MESSAGE,
@FilePath AS [FilePath],
'Saved to ' + @FilePath AS Info
FROM Util.FS.AppendAllTextToFile(@FilePath, @SQL, 1)
GO
EXEC sys.sp_ms_marksystemobject sp_Compare
GO