May 12, 2016 at 3:24 pm
Comments posted to this topic are about the item Table Comparison Procedure
Sheraz Mirza::hehe:
May 26, 2016 at 4:52 pm
This can be done easily using Microsoft Visual Studio Data Comparison.
Thanks.
June 3, 2016 at 9:11 am
We never say It can not be done by using any other tool, but this is programmatic way to get comparison, and will be helpfull if you don't have any other supporting tool or you are restricted to use only sql queries.
Sheraz Mirza::hehe:
June 7, 2016 at 3:08 am
Am I missing something or does the script mishandle nulls?
Basically, it's always generating "where x = isnull(y, 0)" comparisons which evaluate true for x=0 and y=null.
Paul White has a great post about using intersect for this purpose: Undocumented Query Plans: Equality Comparisons
Something along the lines of:
select
*
from
tblA a
join tblB b on a.PK = b.PK
where
not exists (
select a.*-- assuming tblA and tblB have the same structure
intersect
select b.*-- assuming tblA and tblB have the same structure
)
June 8, 2016 at 7:24 am
Yes you are right it is comparing x= isnull(y,0), but that is why because i got that situation. but if somebody wanna compare without converting nulls, select statement is also there, which can be modified easily. Statement you told is very good, but still it is static and need to be modified to be generic/ or dynamic.
Sheraz Mirza::hehe:
June 13, 2016 at 3:29 am
Of course.
Here's an outline of how the stored procedure could look. One should probably add more (better say any;) ) input verification, but--as far as I've tested it--it works correctly.
You could also extend it to take column names for each table as parameters (comma-delimited strings or table-valued parameters or whatever) but then you'd also need to do extra extra validating because then it really could turn into a mess.
CREATE PROCEDURE [dbo].[CompareTables] (
@srcDBsysname,
@srcSchemasysname,
@srcTablesysname,
@destDBsysname,
@destSchemasysname,
@destTablesysname,
@printOnlybit = 0
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE
@sqlMetanvarchar(max),
@sqlDatanvarchar(max),
@srcColsnvarchar(max),
@destColsnvarchar(max),
@diffnvarchar(max),
@joinOnnvarchar(max),
@srcPKNullnvarchar(max),
@destPKNullnvarchar(max);
IF OBJECT_ID('tempdb..#columns', N'U') IS NOT NULL DROP TABLE #columns;
CREATE TABLE #columns (
srcColumnsysnameNOT NULL,
srcTypenvarchar(128)NOT NULL,
srcIsPKbitNOT NULL,
destColumnsysnameNOT NULL,
destTypenvarchar(128)NOT NULL,
destIsPKbitNOT NULL
);
SET @sqlMeta = N'
WITH srcCols AS
(
SELECT
col.COLUMN_NAME,
col.DATA_TYPE,
isPK = CAST(CASE WHEN colPk.COLUMN_NAME IS NOT NULL THEN 1 ELSE 0 END AS bit)
FROM
' + QUOTENAME(@srcDB) + '.INFORMATION_SCHEMA.COLUMNS col
LEFT JOIN (
' + QUOTENAME(@srcDB) + '.INFORMATION_SCHEMA.TABLE_CONSTRAINTS cxPk
JOIN ' + QUOTENAME(@srcDB) + '.INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE colPk ON cxPk.CONSTRAINT_CATALOG = colPk.CONSTRAINT_CATALOG
AND cxPk.CONSTRAINT_SCHEMA = colPk.CONSTRAINT_SCHEMA
AND cxPk.TABLE_NAME = colPk.TABLE_NAME
AND cxPk.CONSTRAINT_NAME = colPk.CONSTRAINT_NAME
) ON col.TABLE_CATALOG = cxPk.TABLE_CATALOG
AND col.TABLE_SCHEMA = cxPk.TABLE_SCHEMA
AND cxPk.CONSTRAINT_TYPE = ''PRIMARY KEY''
AND col.TABLE_NAME = cxPk.TABLE_NAME
AND col.COLUMN_NAME = colPk.COLUMN_NAME
WHERE
col.TABLE_SCHEMA = @srcSchema
AND col.TABLE_NAME = @srcTable
)
, destCols AS
(
SELECT
col.COLUMN_NAME,
col.DATA_TYPE,
isPK = CAST(CASE WHEN colPk.COLUMN_NAME IS NOT NULL THEN 1 ELSE 0 END AS bit)
FROM
' + QUOTENAME(@destDB) + '.INFORMATION_SCHEMA.COLUMNS col
LEFT JOIN (
' + QUOTENAME(@destDB) + '.INFORMATION_SCHEMA.TABLE_CONSTRAINTS cxPk
JOIN ' + QUOTENAME(@destDB) + '.INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE colPk ON cxPk.CONSTRAINT_CATALOG = colPk.CONSTRAINT_CATALOG
AND cxPk.CONSTRAINT_SCHEMA = colPk.CONSTRAINT_SCHEMA
AND cxPk.TABLE_NAME = colPk.TABLE_NAME
AND cxPk.CONSTRAINT_NAME = colPk.CONSTRAINT_NAME
) ON col.TABLE_CATALOG = cxPk.TABLE_CATALOG
AND col.TABLE_SCHEMA = cxPk.TABLE_SCHEMA
AND cxPk.CONSTRAINT_TYPE = ''PRIMARY KEY''
AND col.TABLE_NAME = cxPk.TABLE_NAME
AND col.COLUMN_NAME = colPk.COLUMN_NAME
WHERE
col.TABLE_SCHEMA = @destSchema
AND col.TABLE_NAME = @destTable
)
SELECT
srcColumn= src.COLUMN_NAME,
srcType= src.DATA_TYPE,
srcIsPK= src.isPK,
destColumn= dest.COLUMN_NAME,
destType= dest.DATA_TYPE,
destIsPk= dest.isPK
FROM
srcCols src
FULL JOIN destCols dest ON src.COLUMN_NAME = dest.COLUMN_NAME
AND src.DATA_TYPE = dest.DATA_TYPE
AND src.isPK = dest.isPK
ORDER BY
srcIsPK DESC,
srcColumn ASC;
';
BEGIN TRY
INSERT INTO #columns
(srcColumn, srcType, srcIsPK, destColumn, destType, destIsPK)
EXEC sp_executesql @sqlMeta, N'@srcSchema sysname, @srcTable sysname, @destSchema sysname, @destTable sysname', @srcSchema, @srcTable, @destSchema, @destTable;
IF NOT EXISTS (SELECT 1 FROM #columns) OR NOT EXISTS(SELECT 1 FROM #columns WHERE srcIsPK = 1)
RAISERROR ('Tables missing? Primary key missing?', 16, 1);
END TRY
BEGIN CATCH
RAISERROR ('Something went wrong :( Objects don''t exist, columns are missing, column types don''t match or primary keys don''t match.', 16, 1);
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
RETURN;
END CATCH;
SET @srcCols = (SELECT STUFF(x.string, 1, 2, '')
FROM (
SELECT
', src.' + QUOTENAME(cols.srcColumn)
FROM
#columns cols
FOR XML PATH ('')
) x(string)
);
SET @destCols = (SELECT STUFF(x.string, 1, 2, '')
FROM (
SELECT
', dest.' + QUOTENAME(cols.destColumn)
FROM
#columns cols
FOR XML PATH ('')
) x(string)
);
SET @diff = (SELECT --STUFF(x.string, 1, 1, '')
x.string
FROM (
SELECT
NCHAR(10)-- newline
+ NCHAR(9)
+ NCHAR(9)-- tabs for indenting
+ N'+ CASE WHEN NOT EXISTS (SELECT src.' + QUOTENAME(cols.srcColumn) + ' INTERSECT SELECT dest.' + QUOTENAME(cols.destColumn) + ') THEN ' + QUOTENAME(cols.srcColumn + '; ', '''') + ' ELSE '''' END'
FROM
#columns cols
FOR XML PATH ('')
) x(string)
);
SET @joinOn = (SELECT STUFF(x.string, 1, 5, '')
FROM (
SELECT
N' AND src.' + QUOTENAME(cols.srcColumn) + ' = dest.' + QUOTENAME(cols.destColumn)
FROM
#columns cols
WHERE
cols.srcIsPK = 1
FOR XML PATH ('')
) x(string)
);
SET @srcPKNull = (SELECT STUFF(x.string, 1, 5, '')
FROM (
SELECT
N' AND src.' + QUOTENAME(cols.srcColumn) + ' IS NULL'
FROM
#columns cols
WHERE
cols.srcIsPK = 1
FOR XML PATH ('')
) x(string)
);
SET @destPKNull = (SELECT STUFF(x.string, 1, 5, '')
FROM (
SELECT
N' AND dest.' + QUOTENAME(cols.destColumn) + ' IS NULL'
FROM
#columns cols
WHERE
cols.destIsPK = 1
FOR XML PATH ('')
) x(string)
);
IF @srcCols IS NULL OR @destCols IS NULL OR @diff IS NULL OR @joinOn IS NULL
BEGIN
RAISERROR ('@srcCols IS NULL OR @destCols IS NULL OR @diff IS NULL OR @joinOn IS NULL', 16, 1);
RETURN;
END;
/*** 1. Rows with differences ***/
SET @sqlData = N'
SELECT
' + @srcCols + ',
' + @destCols + ',
diff = '''' ' + @diff + '
FROM
' + QUOTENAME(@srcDB) + '.' + QUOTENAME(@srcSchema) + '.' + QUOTENAME(@srcTable) + ' src
JOIN ' + QUOTENAME(@destDB) + '.' + QUOTENAME(@destSchema) + '.' + QUOTENAME(@destTable) + ' dest ON ' + @joinOn + '
WHERE
NOT EXISTS (
SELECT ' + @srcCols + '
INTERSECT
SELECT ' + @destCols + '
);
';
-- TODO: As the query generated can be quite lengthy, PRINT probably won't print out the whole query due to SQL Server limitations (https://msdn.microsoft.com/en-us/library/ms176047.aspx#Anchor_2)
-- TODO: To get around the 8000/4000 char/nchar limitation you can replace this with a call to the LongPrint procedure found at: http://www.sqlservercentral.com/scripts/Print/63240/
PRINT '/*** 1. Rows with differences ***/';
PRINT @sqlData;
IF @printOnly = 0
EXEC sp_executesql @sqlData;
/*** 2. Rows missing in source ***/
SET @sqlData = N'
SELECT
' + @srcCols + ',
' + @destCols + ',
diff = ''Row missing in source (' + QUOTENAME(@srcDB) + '.' + QUOTENAME(@srcSchema) + '.' + QUOTENAME(@srcTable) + ').''
FROM
' + QUOTENAME(@srcDB) + '.' + QUOTENAME(@srcSchema) + '.' + QUOTENAME(@srcTable) + ' src
LEFT JOIN ' + QUOTENAME(@destDB) + '.' + QUOTENAME(@destSchema) + '.' + QUOTENAME(@destTable) + ' dest ON ' + @joinOn + '
WHERE
' + @destPKNull + ';
';
-- TODO: As the query generated can be quite lengthy, PRINT probably won't print out the whole query due to SQL Server limitations (https://msdn.microsoft.com/en-us/library/ms176047.aspx#Anchor_2)
-- TODO: To get around the 8000/4000 char/nchar limitation you can replace this with a call to the LongPrint procedure found at: http://www.sqlservercentral.com/scripts/Print/63240/
PRINT '/*** 2. Rows missing in source (' + QUOTENAME(QUOTENAME(@srcDB) + '.' + QUOTENAME(@srcSchema) + '.' + QUOTENAME(@srcTable), '''') + ') ***/';
PRINT @sqlData;
IF @printOnly = 0
EXEC sp_executesql @sqlData;
/*** 3. Rows missing in dest ***/
SET @sqlData = N'
SELECT
' + @srcCols + ',
' + @destCols + ',
diff = ''Row missing in destination (' + QUOTENAME(@destDB) + '.' + QUOTENAME(@destSchema) + '.' + QUOTENAME(@destTable) + ').''
FROM
' + QUOTENAME(@destDB) + '.' + QUOTENAME(@destSchema) + '.' + QUOTENAME(@destTable) + ' dest
LEFT JOIN ' + QUOTENAME(@srcDB) + '.' + QUOTENAME(@srcSchema) + '.' + QUOTENAME(@srcTable) + ' src ON ' + @joinOn + '
WHERE
' + @srcPKNull + ';
';
-- TODO: As the query generated can be quite lengthy, PRINT probably won't print out the whole query due to SQL Server limitations (https://msdn.microsoft.com/en-us/library/ms176047.aspx#Anchor_2)
-- TODO: To get around the 8000/4000 char/nchar limitation you can replace this with a call to the LongPrint procedure found at: http://www.sqlservercentral.com/scripts/Print/63240/
PRINT '/*** 3. Rows missing in destination (' + QUOTENAME(QUOTENAME(@destDB) + '.' + QUOTENAME(@destSchema) + '.' + QUOTENAME(@destTable), '''') + ') ***/';
PRINT @sqlData;
IF @printOnly = 0
EXEC sp_executesql @sqlData;
END
June 14, 2016 at 9:10 am
If the tables have the same columns, comparison is easy:
select * from A
except
select * from B
--
union all
--
select * from B
except
select * from A
;
Moreover, this approach works also on tables without a primary key.
June 14, 2016 at 12:03 pm
Thank you for this. It saved so much time for me
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply