Skip to content

Instantly share code, notes, and snippets.

@radityopw
Last active February 19, 2021 01:59
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save radityopw/a230d56b7e51a0abd9a9824e1418767c to your computer and use it in GitHub Desktop.
Save radityopw/a230d56b7e51a0abd9a9824e1418767c to your computer and use it in GitHub Desktop.
sql script to investigate String or binary data would be truncated
;WITH CTE_Dev
AS (
SELECT C.column_id
,ColumnName = C.NAME
,C.max_length
,C.user_type_id
,C.precision
,C.scale
,DataTypeName = T.NAME
FROM sys.columns C
INNER JOIN sys.types T ON T.user_type_id = C.user_type_id
WHERE OBJECT_ID = OBJECT_ID('YOUR TARGET TABLE NAME HERE, WITH SCHEMA')
)
,CTE_Temp
AS (
SELECT C.column_id
,ColumnName = C.NAME
,C.max_length
,C.user_type_id
,C.precision
,C.scale
,DataTypeName = T.NAME
FROM sys.columns C
INNER JOIN sys.types T ON T.user_type_id = C.user_type_id
WHERE OBJECT_ID = OBJECT_ID('YOUR TEMP TABLE NAME HERE, WITH SCHEMA')
)
SELECT *
FROM CTE_Dev D
FULL OUTER JOIN CTE_Temp T ON D.ColumnName = T.ColumnName
WHERE ISNULL(D.max_length, 0) < ISNULL(T.max_length, 999)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment