SQL Server 对比数据库差异语句

1、对比表结构差异：

-- 对比两个数据库中的表差异
SELECT
    t1.name AS TableName,
    t1.object_id AS ObjectId,
    CASE WHEN t2.name IS NULL THEN 'Only in Database 1' ELSE 'Different' END AS [Status]
FROM
    Database1.sys.tables t1
    LEFT JOIN Database2.sys.tables t2 ON t1.name = t2.name
UNION ALL
SELECT
    t2.name AS TableName,
    t2.object_id AS ObjectId,
    'Only in Database 2' AS [Status]
FROM
    Database2.sys.tables t2
    LEFT JOIN Database1.sys.tables t1 ON t2.name = t1.name
WHERE
    t1.name IS NULL;

2、对比表数据差异：

-- 对比两个表的数据差异
SELECT *
FROM Database1.dbo.TableName
EXCEPT
SELECT *
FROM Database2.dbo.TableName;

3、对比存储过程差异：

-- 对比两个数据库中的存储过程差异
SELECT
    p1.name AS ProcedureName,
    p1.object_id AS ObjectId,
    CASE WHEN p2.name IS NULL THEN 'Only in Database 1' ELSE 'Different' END AS [Status]
FROM
    Database1.sys.procedures p1
    LEFT JOIN Database2.sys.procedures p2 ON p1.name = p2.name
UNION ALL
SELECT
    p2.name AS ProcedureName,
    p2.object_id AS ObjectId,
    'Only in Database 2' AS [Status]
FROM
    Database2.sys.procedures p2
    LEFT JOIN Database1.sys.procedures p1 ON p2.name = p1.name
WHERE
    p1.name IS NULL;