快速而肮脏的答案:这将逐列创建和比较两个表(必须具有与您指示的模式相同)的值。
它仅显示两个不相等的值,而不是所有列。这不包含空处理或错误处理。同样不建议在可能受到sql注入的情况下使用此方法。一旦测试了它,就取消对EXEC的注释以运行动态sql。
USE TEMPDB
GO
DECLARE @sql NVARCHAR(MAX), @sql_OR NVARCHAR(MAX), @sql_CASE NVARCHAR(MAX)
SET @sql=''
SET @sql_OR=''
SET @sql_CASE=''
IF OBJECT_ID('tempdb.dbo.tmp1') IS NOT NULL DROP TABLE tempdb.dbo.tmp1
IF OBJECT_ID('tempdb.dbo.tmp2') IS NOT NULL DROP TABLE tempdb.dbo.tmp2
CREATE TABLE tempdb.dbo.tmp1 (keyField int identity(1,1), value1 int, value2 int)
CREATE TABLE tempdb.dbo.tmp2 (keyField int identity(1,1), value1 int, value2 int)
INSERT INTO tempdb.dbo.tmp1 (value1, value2)
VALUES (555,1204),
(999,1255),
(666,9999),
(12345,12345)
INSERT INTO tempdb.dbo.tmp2 (value1, value2)
VALUES (555,1205),
(888,1255),
(666,9999),
(12345,NULL)
SELECT @sql_OR=@sql_OR+' OR ISNULL(T1.['+TBL1.COLUMN_NAME+'],'''')!=ISNULL(T2.['+TBL1.COLUMN_NAME+'],'''')' + CHAR(13),
@sql_CASE=@sql_CASE+', CASE WHEN ISNULL(T1.['+TBL1.COLUMN_NAME+'],'''')!=ISNULL(T2.['+TBL1.COLUMN_NAME+'],'''') THEN ISNULL(CONVERT(NVARCHAR,T1.['+TBL1.COLUMN_NAME+']),''NULL'')+'' != ''+ISNULL(CONVERT(NVARCHAR,T2.['+TBL1.COLUMN_NAME+']),''NULL'') ELSE NULL END AS ['+TBL1.COLUMN_NAME+']' + CHAR(13)
FROM INFORMATION_SCHEMA.COLUMNS TBL1
WHERE TBL1.TABLE_NAME='tmp1'
AND TBL1.COLUMN_NAME!='keyField'
AND EXISTS (SELECT 1
FROM INFORMATION_SCHEMA.COLUMNS TBL2
WHERE TBL2.TABLE_NAME='tmp2'
AND TBL2.COLUMN_NAME!='keyField'
AND TBL1.COLUMN_NAME=TBL2.COLUMN_NAME)
SET @sql = 'SELECT T1.keyField'+@sql_CASE+'
FROM tempdb.dbo.tmp1 T1
LEFT JOIN tempdb.dbo.tmp2 T2
ON T1.keyField=T2.keyField
WHERE 1=2' + @sql_OR
PRINT @sql
--EXEC(@sql)
输出:
keyField value1 value2
1 NULL 1204 != 1205
2 999 != 888 NULL
4 NULL 12345 != NULL