如何用T-SQL程序化对比两张表的完整定义？

当然可以实现，sp_help返回的所有表定义信息都能通过SQL Server的系统视图/函数提取，然后通过集合操作或连接查询找出两张表的差异。下面是具体的实现思路和代码示例，覆盖sp_help返回的核心内容：

核心思路 #

sp_help的结果集本质是查询系统元数据视图生成的，所以我们可以直接从sys.tables、sys.columns、sys.indexes等系统对象中提取对应信息，然后对两张表的同类元数据做对比。

具体实现示例 #

下面的代码会分模块对比列定义、索引/主键、外键、检查约束、触发器等核心内容，你可以封装成存储过程或脚本执行。

1. 对比列定义（含数据类型、空值、默认值、标识列） #

DECLARE @SchemaName1 NVARCHAR(128) = 'dbo', @TableName1 NVARCHAR(128) = 'TableA';
DECLARE @SchemaName2 NVARCHAR(128) = 'dbo', @TableName2 NVARCHAR(128) = 'TableB';

-- 表1独有的列定义
SELECT 'TableA 独有列' AS diff_type,
       c.name AS column_name,
       t.name AS data_type,
       CASE WHEN c.max_length = -1 THEN 'MAX' ELSE CAST(c.max_length AS VARCHAR(10)) END AS max_length,
       CAST(c.precision AS VARCHAR(10)) AS precision,
       CAST(c.scale AS VARCHAR(10)) AS scale,
       CASE c.is_nullable WHEN 1 THEN '允许空' ELSE '不允许空' END AS is_nullable,
       ISNULL(dc.definition, '') AS default_value,
       CASE c.is_identity WHEN 1 THEN '是' ELSE '否' END AS is_identity,
       ISNULL(CAST(IDENT_SEED(QUOTENAME(@SchemaName1) + '.' + QUOTENAME(@TableName1)) AS VARCHAR(20)), '') AS identity_seed,
       ISNULL(CAST(IDENT_INCR(QUOTENAME(@SchemaName1) + '.' + QUOTENAME(@TableName1)) AS VARCHAR(20)), '') AS identity_increment
FROM sys.objects o
JOIN sys.schemas s ON o.schema_id = s.schema_id
JOIN sys.columns c ON o.object_id = c.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.default_constraints dc ON c.default_object_id = dc.object_id
WHERE o.name = @TableName1 AND s.name = @SchemaName1
EXCEPT
SELECT 'TableA 独有列' AS diff_type,
       c.name AS column_name,
       t.name AS data_type,
       CASE WHEN c.max_length = -1 THEN 'MAX' ELSE CAST(c.max_length AS VARCHAR(10)) END AS max_length,
       CAST(c.precision AS VARCHAR(10)) AS precision,
       CAST(c.scale AS VARCHAR(10)) AS scale,
       CASE c.is_nullable WHEN 1 THEN '允许空' ELSE '不允许空' END AS is_nullable,
       ISNULL(dc.definition, '') AS default_value,
       CASE c.is_identity WHEN 1 THEN '是' ELSE '否' END AS is_identity,
       ISNULL(CAST(IDENT_SEED(QUOTENAME(@SchemaName2) + '.' + QUOTENAME(@TableName2)) AS VARCHAR(20)), '') AS identity_seed,
       ISNULL(CAST(IDENT_INCR(QUOTENAME(@SchemaName2) + '.' + QUOTENAME(@TableName2)) AS VARCHAR(20)), '') AS identity_increment
FROM sys.objects o
JOIN sys.schemas s ON o.schema_id = s.schema_id
JOIN sys.columns c ON o.object_id = c.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.default_constraints dc ON c.default_object_id = dc.object_id
WHERE o.name = @TableName2 AND s.name = @SchemaName2

UNION ALL

-- 表2独有的列定义
SELECT 'TableB 独有列' AS diff_type,
       c.name AS column_name,
       t.name AS data_type,
       CASE WHEN c.max_length = -1 THEN 'MAX' ELSE CAST(c.max_length AS VARCHAR(10)) END AS max_length,
       CAST(c.precision AS VARCHAR(10)) AS precision,
       CAST(c.scale AS VARCHAR(10)) AS scale,
       CASE c.is_nullable WHEN 1 THEN '允许空' ELSE '不允许空' END AS is_nullable,
       ISNULL(dc.definition, '') AS default_value,
       CASE c.is_identity WHEN 1 THEN '是' ELSE '否' END AS is_identity,
       ISNULL(CAST(IDENT_SEED(QUOTENAME(@SchemaName2) + '.' + QUOTENAME(@TableName2)) AS VARCHAR(20)), '') AS identity_seed,
       ISNULL(CAST(IDENT_INCR(QUOTENAME(@SchemaName2) + '.' + QUOTENAME(@TableName2)) AS VARCHAR(20)), '') AS identity_increment
FROM sys.objects o
JOIN sys.schemas s ON o.schema_id = s.schema_id
JOIN sys.columns c ON o.object_id = c.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.default_constraints dc ON c.default_object_id = dc.object_id
WHERE o.name = @TableName2 AND s.name = @SchemaName2
EXCEPT
SELECT 'TableB 独有列' AS diff_type,
       c.name AS column_name,
       t.name AS data_type,
       CASE WHEN c.max_length = -1 THEN 'MAX' ELSE CAST(c.max_length AS VARCHAR(10)) END AS max_length,
       CAST(c.precision AS VARCHAR(10)) AS precision,
       CAST(c.scale AS VARCHAR(10)) AS scale,
       CASE c.is_nullable WHEN 1 THEN '允许空' ELSE '不允许空' END AS is_nullable,
       ISNULL(dc.definition, '') AS default_value,
       CASE c.is_identity WHEN 1 THEN '是' ELSE '否' END AS is_identity,
       ISNULL(CAST(IDENT_SEED(QUOTENAME(@SchemaName1) + '.' + QUOTENAME(@TableName1)) AS VARCHAR(20)), '') AS identity_seed,
       ISNULL(CAST(IDENT_INCR(QUOTENAME(@SchemaName1) + '.' + QUOTENAME(@TableName1)) AS VARCHAR(20)), '') AS identity_increment
FROM sys.objects o
JOIN sys.schemas s ON o.schema_id = s.schema_id
JOIN sys.columns c ON o.object_id = c.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.default_constraints dc ON c.default_object_id = dc.object_id
WHERE o.name = @TableName1 AND s.name = @SchemaName1;

2. 对比索引与主键定义 #

-- 表1独有的索引/主键
SELECT 'TableA 独有索引' AS diff_type,
       i.name AS index_name,
       i.type_desc AS index_type,
       STRING_AGG(c.name, ', ') WITHIN GROUP (ORDER BY ic.key_ordinal) AS key_columns,
       STRING_AGG(CASE WHEN ic.is_included_column = 1 THEN c.name ELSE NULL END, ', ') WITHIN GROUP (ORDER BY ic.key_ordinal) AS included_columns,
       CASE i.is_unique WHEN 1 THEN '唯一' ELSE '非唯一' END AS is_unique,
       CASE i.is_primary_key WHEN 1 THEN '主键' ELSE '非主键' END AS is_primary_key,
       CAST(i.fill_factor AS VARCHAR(3)) AS fill_factor
FROM sys.objects o
JOIN sys.schemas s ON o.schema_id = s.schema_id
JOIN sys.indexes i ON o.object_id = i.object_id
JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
JOIN sys.columns c ON o.object_id = c.object_id AND ic.column_id = c.column_id
WHERE o.name = @TableName1 AND s.name = @SchemaName1 AND i.type <> 0 -- 排除堆表的默认索引
GROUP BY i.name, i.type_desc, i.is_unique, i.is_primary_key, i.fill_factor
EXCEPT
SELECT 'TableA 独有索引' AS diff_type,
       i.name AS index_name,
       i.type_desc AS index_type,
       STRING_AGG(c.name, ', ') WITHIN GROUP (ORDER BY ic.key_ordinal) AS key_columns,
       STRING_AGG(CASE WHEN ic.is_included_column = 1 THEN c.name ELSE NULL END, ', ') WITHIN GROUP (ORDER BY ic.key_ordinal) AS included_columns,
       CASE i.is_unique WHEN 1 THEN '唯一' ELSE '非唯一' END AS is_unique,
       CASE i.is_primary_key WHEN 1 THEN '主键' ELSE '非主键' END AS is_primary_key,
       CAST(i.fill_factor AS VARCHAR(3)) AS fill_factor
FROM sys.objects o
JOIN sys.schemas s ON o.schema_id = s.schema_id
JOIN sys.indexes i ON o.object_id = i.object_id
JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
JOIN sys.columns c ON o.object_id = c.object_id AND ic.column_id = c.column_id
WHERE o.name = @TableName2 AND s.name = @SchemaName2 AND i.type <> 0
GROUP BY i.name, i.type_desc, i.is_unique, i.is_primary_key, i.fill_factor

UNION ALL

-- 表2独有的索引/主键
SELECT 'TableB 独有索引' AS diff_type,
       i.name AS index_name,
       i.type_desc AS index_type,
       STRING_AGG(c.name, ', ') WITHIN GROUP (ORDER BY ic.key_ordinal) AS key_columns,
       STRING_AGG(CASE WHEN ic.is_included_column = 1 THEN c.name ELSE NULL END, ', ') WITHIN GROUP (ORDER BY ic.key_ordinal) AS included_columns,
       CASE i.is_unique WHEN 1 THEN '唯一' ELSE '非唯一' END AS is_unique,
       CASE i.is_primary_key WHEN 1 THEN '主键' ELSE '非主键' END AS is_primary_key,
       CAST(i.fill_factor AS VARCHAR(3)) AS fill_factor
FROM sys.objects o
JOIN sys.schemas s ON o.schema_id = s.schema_id
JOIN sys.indexes i ON o.object_id = i.object_id
JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
JOIN sys.columns c ON o.object_id = c.object_id AND ic.column_id = c.column_id
WHERE o.name = @TableName2 AND s.name = @SchemaName2 AND i.type <> 0
GROUP BY i.name, i.type_desc, i.is_unique, i.is_primary_key, i.fill_factor
EXCEPT
SELECT 'TableB 独有索引' AS diff_type,
       i.name AS index_name,
       i.type_desc AS index_type,
       STRING_AGG(c.name, ', ') WITHIN GROUP (ORDER BY ic.key_ordinal) AS key_columns,
       STRING_AGG(CASE WHEN ic.is_included_column = 1 THEN c.name ELSE NULL END, ', ') WITHIN GROUP (ORDER BY ic.key_ordinal) AS included_columns,
       CASE i.is_unique WHEN 1 THEN '唯一' ELSE '非唯一' END AS is_unique,
       CASE i.is_primary_key WHEN 1 THEN '主键' ELSE '非主键' END AS is_primary_key,
       CAST(i.fill_factor AS VARCHAR(3)) AS fill_factor
FROM sys.objects o
JOIN sys.schemas s ON o.schema_id = s.schema_id
JOIN sys.indexes i ON o.object_id = i.object_id
JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
JOIN sys.columns c ON o.object_id = c.object_id AND ic.column_id = c.column_id
WHERE o.name = @TableName1 AND s.name = @SchemaName1 AND i.type <> 0
GROUP BY i.name, i.type_desc, i.is_unique, i.is_primary_key, i.fill_factor;

3. 对比外键约束 #

-- 表1独有的外键
SELECT 'TableA 独有外键' AS diff_type,
       fk.name AS fk_name,
       c.name AS parent_column,
       OBJECT_NAME(fk.referenced_object_id) AS referenced_table,
       rc.name AS referenced_column
FROM sys.foreign_keys fk
JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
JOIN sys.columns c ON fkc.parent_object_id = c.object_id AND fkc.parent_column_id = c.column_id
JOIN sys.columns rc ON fkc.referenced_object_id = rc.object_id AND fkc.referenced_column_id = rc.column_id
JOIN sys.objects o ON fk.parent_object_id = o.object_id
JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE o.name = @TableName1 AND s.name = @SchemaName1
EXCEPT
SELECT 'TableA 独有外键' AS diff_type,
       fk.name AS fk_name,
       c.name AS parent_column,
       OBJECT_NAME(fk.referenced_object_id) AS referenced_table,
       rc.name AS referenced_column
FROM sys.foreign_keys fk
JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
JOIN sys.columns c ON fkc.parent_object_id = c.object_id AND fkc.parent_column_id = c.column_id
JOIN sys.columns rc ON fkc.referenced_object_id = rc.object_id AND fkc.referenced_column_id = rc.column_id
JOIN sys.objects o ON fk.parent_object_id = o.object_id
JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE o.name = @TableName2 AND s.name = @SchemaName2

UNION ALL

-- 表2独有的外键
SELECT 'TableB 独有外键' AS diff_type,
       fk.name AS fk_name,
       c.name AS parent_column,
       OBJECT_NAME(fk.referenced_object_id) AS referenced_table,
       rc.name AS referenced_column
FROM sys.foreign_keys fk
JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
JOIN sys.columns c ON fkc.parent_object_id = c.object_id AND fkc.parent_column_id = c.column_id
JOIN sys.columns rc ON fkc.referenced_object_id = rc.object_id AND fkc.referenced_column_id = rc.column_id
JOIN sys.objects o ON fk.parent_object_id = o.object_id
JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE o.name = @TableName2 AND s.name = @SchemaName2
EXCEPT
SELECT 'TableB 独有外键' AS diff_type,
       fk.name AS fk_name,
       c.name AS parent_column,
       OBJECT_NAME(fk.referenced_object_id) AS referenced_table,
       rc.name AS referenced_column
FROM sys.foreign_keys fk
JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
JOIN sys.columns c ON fkc.parent_object_id = c.object_id AND fkc.parent_column_id = c.column_id
JOIN sys.columns rc ON fkc.referenced_object_id = rc.object_id AND fkc.referenced_column_id = rc.column_id
JOIN sys.objects o ON fk.parent_object_id = o.object_id
JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE o.name = @TableName1 AND s.name = @SchemaName1;

4. 对比检查约束 #

-- 表1独有的检查约束
SELECT 'TableA 独有检查约束' AS diff_type,
       cc.name AS constraint_name,
       cc.definition
FROM sys.check_constraints cc
JOIN sys.objects o ON cc.parent_object_id = o.object_id
JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE o.name = @TableName1 AND s.name = @SchemaName1
EXCEPT
SELECT 'TableA 独有检查约束' AS diff_type,
       cc.name AS constraint_name,
       cc.definition
FROM sys.check_constraints cc
JOIN sys.objects o ON cc.parent_object_id = o.object_id
JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE o.name = @TableName2 AND s.name = @SchemaName2

UNION ALL

-- 表2独有的检查约束
SELECT 'TableB 独有检查约束' AS diff_type,
       cc.name AS constraint_name,
       cc.definition
FROM sys.check_constraints cc
JOIN sys.objects o ON cc.parent_object_id = o.object_id
JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE o.name = @TableName2 AND s.name = @SchemaName2
EXCEPT
SELECT 'TableB 独有检查约束' AS diff_type,
       cc.name AS constraint_name,
       cc.definition
FROM sys.check_constraints cc
JOIN sys.objects o ON cc.parent_object_id = o.object_id
JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE o.name = @TableName1 AND s.name = @SchemaName1;

5. 对比触发器 #

-- 表1独有的触发器
SELECT 'TableA 独有触发器' AS diff_type,
       tr.name AS trigger_name,
       tr.type_desc AS trigger_type,
       tr.create_date
FROM sys.triggers tr
JOIN sys.objects o ON tr.parent_id = o.object_id
JOIN sys.schemas s ON o.schema_id = s.schema_id