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 )。
简单吧？
181
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
