-- 声明需要对比的两个表名（修改为你的实际表名，确保在同一数据库下）
DECLARE @Table1 NVARCHAR(128) = N'sjjh_jhuser';
DECLARE @Table2 NVARCHAR(128) = N'sjjh_jhuser_bak';
-- 提取表字段的核心配置，进行对比
WITH Table1Columns AS (
SELECT
c.name AS 字段名,
t.name AS 数据类型,
c.max_length AS 字段长度, -- 字段定义长度（varchar/nvarchar需注意：nvarchar长度为字节数，实际字符数为max_length/2）
c.is_nullable AS 允许为空, -- 0=不允许为空，1=允许为空
c.column_id AS 字段顺序, -- 字段物理存储顺序（select * 依赖此顺序）
CASE WHEN pk.column_id IS NOT NULL THEN 1 ELSE 0 END AS 是否主键
FROM
sys.columns c
JOIN
sys.tables tbl ON c.object_id = tbl.object_id
JOIN
sys.types t ON c.system_type_id = t.system_type_id AND c.user_type_id = t.user_type_id
LEFT JOIN
sys.indexes idx ON tbl.object_id = idx.object_id AND idx.is_primary_key = 1
LEFT JOIN
sys.index_columns pk ON idx.object_id = pk.object_id AND idx.index_id = pk.index_id AND c.column_id = pk.column_id
WHERE
tbl.name = @Table1
),
Table2Columns AS (
SELECT
c.name AS 字段名,
t.name AS 数据类型,
c.max_length AS 字段长度,
c.is_nullable AS 允许为空,
c.column_id AS 字段顺序,
CASE WHEN pk.column_id IS NOT NULL THEN 1 ELSE 0 END AS 是否主键
FROM
sys.columns c
JOIN
sys.tables tbl ON c.object_id = tbl.object_id
JOIN
sys.types t ON c.system_type_id = t.system_type_id AND c.user_type_id = t.user_type_id
LEFT JOIN
sys.indexes idx ON tbl.object_id = idx.object_id AND idx.is_primary_key = 1
LEFT JOIN
sys.index_columns pk ON idx.object_id = pk.object_id AND idx.index_id = pk.index_id AND c.column_id = pk.column_id
WHERE
tbl.name = @Table2
)
-- 1. 查询两个表的差异字段（包含：源表有目标表无、目标表有源表无、字段属性不一致）
SELECT
CASE
WHEN t1.字段名 IS NULL THEN '目标表独有'
WHEN t2.字段名 IS NULL THEN '源表独有（目标表缺失）'
ELSE '字段属性不一致'
END AS 差异类型,
ISNULL(t1.字段名, t2.字段名) AS 字段名,
t1.数据类型 AS 源表数据类型,
t2.数据类型 AS 目标表数据类型,
t1.字段长度 AS 源表字段长度,
t2.字段长度 AS 目标表字段长度,
t1.允许为空 AS 源表是否允许空,
t2.允许为空 AS 目标表是否允许空,
t1.字段顺序 AS 源表字段顺序,
t2.字段顺序 AS 目标表字段顺序
FROM
Table1Columns t1
FULL OUTER JOIN
Table2Columns t2 ON t1.字段名 = t2.字段名
WHERE
-- 筛选出属性不一致的记录
t1.数据类型 <> t2.数据类型
OR t1.字段长度 <> t2.字段长度
OR t1.允许为空 <> t2.允许为空
OR t1.字段名 IS NULL
OR t2.字段名 IS NULL
ORDER BY
ISNULL(t1.字段顺序, t2.字段顺序);
|
