SQL Server: 将截断字符串或二进制数据，快速判断哪个字段超长

上面的出错， 用 SQL Server 的人基本上都遇到过。如果某个表的字段少还好， 有几十甚至更多字段就抓瞎了，如何快速判断哪个字段超长呢？

先创建一个存储过程：

IF OBJECT_ID('Proc_Util_GetLenOverflowColumns') IS NOT NULL
	DROP PROC Proc_Util_GetLenOverflowColumns
GO
-- =============================================
-- Author:		yenange
-- Create date: 2020-10-09
-- Description:	判断字段名相同的两个表，哪些字段可能会有插入问题
-- =============================================
CREATE PROCEDURE dbo.Proc_Util_GetLenOverflowColumns
	@targetTableName NVARCHAR(100),			--要插入的目标表
	@tmpTableName NVARCHAR(100)				--插入数据生成的临时表
AS
BEGIN
	SET NOCOUNT ON;
	;WITH t1 AS (
		SELECT * FROM sys.columns AS a WHERE a.object_id=OBJECT_ID(@targetTableName)
	),t2 AS (
		SELECT * FROM sys.columns AS b WHERE b.object_id=OBJECT_ID(@tmpTableName)
	)
	SELECT 
		  OBJECT_NAME(t1.OBJECT_ID)  AS targetTableName
		, OBJECT_NAME(t2.OBJECT_ID)  AS tmpTableName
		, t1.name AS columnName
		, t1.max_length AS targetMaxlen
		, t2.max_length AS tmpMaxlen
		, tp1.name AS targetColType
		, tp2.name AS tmpColType
	FROM t1 INNER JOIN t2 ON t1.name=t2.name
		INNER JOIN sys.types AS tp1 ON t1.system_type_id=tp1.system_type_id
		INNER JOIN sys.types AS tp2 ON t2.system_type_id=tp2.system_type_id
	WHERE t1.max_length<t2.max_length
		AND t1.max_length!=-1				--排除原表字段为 max 的情况
		AND tp1.name!='sysname'
		AND tp2.name!='sysname'
END
GO

下面我们来测试下是否能找到可能有问题的字段：

USE tempdb
GO
IF OBJECT_ID('t1') IS NOT NULL
	DROP TABLE t1
GO
CREATE TABLE t1(
	id INT PRIMARY KEY,
	n  NVARCHAR(10) NOT NULL,
	isEnabled BIT NOT NULL	
)
GO
INSERT INTO t1 VALUES(99999999,'12345678901',2)
/*
消息 8152，级别 16，状态 4，第 12 行
将截断字符串或二进制数据。
语句已终止。
*/
--------------- 以上为测试表及模拟插入时产生的错误 -------------

--1. 将需要插入的数据, 先插入到临时用的普通表
--注：字段名要完全与目标表的相同，方便下一步的对比
IF OBJECT_ID('tmp') IS NOT NULL
	DROP TABLE tmp
GO
SELECT * 
INTO tmp
FROM (
	SELECT 99999999 AS id
		,N'12345678901' AS n
		,1 AS isEnabled
	UNION ALL
	SELECT 99999999999 AS id
		,N'12345678901234' AS n
		,0 AS isEnabled
) AS tt
--(1 行受影响)
GO
--2. 比较目标表与临时表的字段差异，获取到可能长度不够的字段名称
EXEC Proc_Util_GetLenOverflowColumns 
	@targetTableName ='t1',		--要插入的目标表
	@tmpTableName ='tmp'		--插入数据生成的临时表
	

结果：

根据结果， 把字段类型或长度对应改一下， 就可以避免出错了。

注：临时产生的表，用完记得删除( drop )。

简单吧？

---------------------------------------- 后记 ---------------------------------------------

今天在Sqlserver 2022 上测试，发现已经有直接显示错误字段的功能了。

还是尽快升级到Sqlserver 2022+吧，越新的版本，人性化的功能越多。

/*
--兼容性级别，先调整到 160（SQLServer2022标准级别）
--调整到 150 也可以，那就是从 SQLServer2019 开始有的功能了
ALTER DATABASE AdventureWorks2022
SET COMPATIBILITY_LEVEL = 160;
GO
*/
IF OBJECT_ID('t1') IS NOT NULL
	DROP TABLE t1
GO
CREATE TABLE t1(
	id INT PRIMARY KEY,
	n  NVARCHAR(10) NOT NULL,
	isEnabled BIT NOT NULL	
)
GO
INSERT INTO t1 VALUES(99999999,'12345678901',2)
/*
消息 2628，级别 16，状态 1，第 10 行
字符串或二进制数据将在表“AdventureWorks2022.dbo.t1”，列“n”中被截断。截断值:“1234567890”。
语句已终止。
*/