这里有一个稍微不同的答案。您的列名和长度可能都匹配，但可能您在SELECT语句中以错误的顺序指定了列。假设tableX和tableY的列名称相同，但顺序不同

至少将数据类型nchar(10)的长度更改为nchar(255)。

我构建了一个存储过程，它分析一个源表或查询，每个列都有几个特征，其中最小长度(min_len)和最大长度(max_len)。

CREATE PROCEDURE [dbo].[sp_analysetable] (
  @tableName varchar(8000),
  @deep bit = 0
) AS

/*
sp_analysetable 'company'
sp_analysetable 'select * from company where name is not null'
*/

DECLARE @intErrorCode INT, @errorMSG VARCHAR(500), @tmpQ NVARCHAR(2000), @column_name VARCHAR(50), @isQuery bit
SET @intErrorCode=0

IF OBJECT_ID('tempdb..##tmpTableToAnalyse') IS NOT NULL BEGIN
  DROP TABLE ##tmpTableToAnalyse
END
IF OBJECT_ID('tempdb..##tmpColumns') IS NOT NULL BEGIN
  DROP TABLE ##tmpColumns
END

if CHARINDEX('from', @tableName)>0
  set @isQuery=1

IF @intErrorCode=0 BEGIN
  if @isQuery=1 begin
    --set @tableName = 'USE '+@db+';'+replace(@tableName, 'from', 'into ##tmpTableToAnalyse from')
    --replace only first occurance. Now multiple froms may exists, but first from will be replaced with into .. from
    set @tableName=Stuff(@tableName, CharIndex('from', @tableName), Len('from'), 'into ##tmpTableToAnalyse from')
    exec(@tableName)
    IF OBJECT_ID('tempdb..##tmpTableToAnalyse') IS NULL BEGIN
      set @intErrorCode=1
      SET @errorMSG='Error generating temporary table from query.'
    end
    else begin
      set @tableName='##tmpTableToAnalyse'
    end
  end
end

IF @intErrorCode=0 BEGIN
  SET @tmpQ='USE '+DB_NAME()+';'+CHAR(13)+CHAR(10)+'
  select
    c.column_name as [column],
    cast(sp.value as varchar(1000)) as description,
    tc_fk.constraint_type,
    kcu_pk.table_name as fk_table,
    kcu_pk.column_name as fk_column,
    c.ordinal_position as pos,
    c.column_default as [default],
    c.is_nullable as [null],
    c.data_type,
    c.character_maximum_length as length,
    c.numeric_precision as [precision],
    c.numeric_precision_radix as radix,
    cast(null as bit) as [is_unique],
    cast(null as int) as min_len,
    cast(null as int) as max_len,
    cast(null as int) as nulls,
    cast(null as int) as blanks,
    cast(null as int) as numerics,
    cast(null as int) as distincts,
    cast(null as varchar(500)) as distinct_values,
    cast(null as varchar(50)) as remarks
  into ##tmpColumns'
  if @isQuery=1 begin
    SET @tmpQ=@tmpQ+' from tempdb.information_schema.columns c, (select null as value) sp'
  end
  else begin
    SET @tmpQ=@tmpQ+'
      from information_schema.columns c
      left join sysobjects so    on so.name=c.table_name  and so.xtype=''U''
      left join syscolumns sc    on sc.name=c.column_name and sc.id  =so.id 
      left join sys.extended_properties sp on sp.minor_id = sc.colid AND sp.major_id = sc.id and sp.name=''MS_Description''  
      left join information_schema.key_column_usage kcu_fk    on kcu_fk.table_name = c.table_name     and c.column_name = kcu_fk.column_name
      left join information_schema.table_constraints tc_fk    on kcu_fk.table_name = tc_fk.table_name and kcu_fk.constraint_name = tc_fk.constraint_name
      left join information_schema.referential_constraints rc on rc.constraint_name = kcu_fk.constraint_name
      left join information_schema.table_constraints tc_pk    on rc.unique_constraint_name = tc_pk.constraint_name
      left join information_schema.key_column_usage kcu_pk    on tc_pk.constraint_name = kcu_pk.constraint_name
 '
  end
  SET @tmpQ=@tmpQ+' where c.table_name = '''+@tableName+''''

  exec(@tmpQ)
end

IF @intErrorCode=0 AND @deep = 1 BEGIN
  DECLARE
    @count_rows int,
    @count_distinct int,
    @count_nulls int,
    @count_blanks int,
    @count_numerics int,
    @min_len int,
    @max_len int,
    @distinct_values varchar(500)
  DECLARE curTmp CURSOR LOCAL FAST_FORWARD FOR
    select [column] from ##tmpColumns;
  OPEN curTmp
  FETCH NEXT FROM curTmp INTO @column_name
  WHILE @@FETCH_STATUS = 0 and @intErrorCode=0 BEGIN
    set @tmpQ = 'USE '+DB_NAME()+'; SELECT'+
      '  @count_rows=count(0), '+char(13)+char(10)+
      '  @count_distinct=count(distinct ['+@column_name+']),'+char(13)+char(10)+
      '  @count_nulls=sum(case when ['+@column_name+'] is null then 1 else 0 end),'+char(13)+char(10)+
      '  @count_blanks=sum(case when ltrim(['+@column_name+'])='''' then 1 else 0 end),'+char(13)+char(10)+
      '  @count_numerics=sum(isnumeric(['+@column_name+'])),'+char(13)+char(10)+
      '  @min_len=min(len(['+@column_name+'])),'+char(13)+char(10)+
      '  @max_len=max(len(['+@column_name+']))'+char(13)+char(10)+
      ' from ['+@tableName+']'
    exec sp_executesql @tmpQ,
                       N'@count_rows int OUTPUT,
                         @count_distinct int OUTPUT,
                         @count_nulls int OUTPUT,
                         @count_blanks int OUTPUT,
                         @count_numerics int OUTPUT,
                         @min_len int OUTPUT,
                         @max_len int OUTPUT',
                       @count_rows     OUTPUT,
                       @count_distinct OUTPUT,
                       @count_nulls    OUTPUT,
                       @count_blanks    OUTPUT,
                       @count_numerics OUTPUT,
                       @min_len        OUTPUT,
                       @max_len        OUTPUT

    IF (@count_distinct>10) BEGIN
      SET @distinct_values='Many ('+cast(@count_distinct as varchar)+')'
    END ELSE BEGIN
      set @distinct_values=null
      set @tmpQ = N'USE '+DB_NAME()+';'+
        '  select @distinct_values=COALESCE(@distinct_values+'',''+cast(['+@column_name+'] as varchar),  cast(['+@column_name+'] as varchar))'+char(13)+char(10)+
        '  from ('+char(13)+char(10)+
        '    select distinct ['+@column_name+'] from ['+@tableName+'] where ['+@column_name+'] is not null) a'+char(13)+char(10)
      exec sp_executesql @tmpQ,
                         N'@distinct_values varchar(500) OUTPUT',
                         @distinct_values        OUTPUT
    END
    UPDATE ##tmpColumns SET
      is_unique      =case when @count_rows=@count_distinct then 1 else 0 end,
      distincts      =@count_distinct,
      nulls          =@count_nulls,
      blanks         =@count_blanks,
      numerics       =@count_numerics,
      min_len        =@min_len,
      max_len        =@max_len,
      distinct_values=@distinct_values,
      remarks       =
        case when @count_rows=@count_nulls then 'all null,' else '' end+
        case when @count_rows=@count_distinct then 'unique,' else '' end+
        case when @count_distinct=0 then 'empty,' else '' end+
        case when @min_len=@max_len then 'same length,' else '' end+
        case when @count_rows=@count_numerics then 'all numeric,' else '' end
    WHERE [column]=@column_name

    FETCH NEXT FROM curTmp INTO @column_name
  END
  CLOSE curTmp DEALLOCATE curTmp
END

IF @intErrorCode=0 BEGIN
  select * from ##tmpColumns order by pos
end

IF @intErrorCode=0 BEGIN --Clean up temporary tables
  IF OBJECT_ID('tempdb..##tmpTableToAnalyse') IS NOT NULL BEGIN
    DROP TABLE ##tmpTableToAnalyse
  END
  IF OBJECT_ID('tempdb..##tmpColumns') IS NOT NULL BEGIN
    DROP TABLE ##tmpColumns
  END
end

IF @intErrorCode<>0 BEGIN
  RAISERROR(@errorMSG, 12, 1)
END
RETURN @intErrorCode

我把这个过程存储在主数据库中，这样我就可以在每个数据库中使用它，就像这样:

sp_analysetable 'table_name', 1
// deep=1 for doing value analyses

输出为:

column description constraint_type fk_table fk_column pos default null data_type length precision radix is_unique min_len max_len nulls blanks numerics distincts distinct_values remarks id_individual NULL PRIMARY KEY NULL NULL 1 NULL NO int NULL 10 10 1 1 2 0 0 70 70 Many (70) unique,all numeric, id_brand NULL NULL NULL NULL 2 NULL NO int NULL 10 10 0 1 1 0 0 70 2 2,3 same length,all numeric, guid NULL NULL NULL NULL 3 (newid()) NO uniqueidentifier NULL NULL NULL 1 36 36 0 0 0 70 Many (70) unique,same length, customer_id NULL NULL NULL NULL 4 NULL YES varchar 50 NULL NULL 0 NULL NULL 70 0 0 0 NULL all null,empty, email NULL NULL NULL NULL 5 NULL YES varchar 100 NULL NULL 0 4 36 0 0 0 31 Many (31) mobile NULL NULL NULL NULL 6 NULL YES varchar 50 NULL NULL 0 NULL NULL 70 0 0 0 NULL all null,empty, initials NULL NULL NULL NULL 7 NULL YES varchar 50 NULL NULL 0 NULL NULL 70 0 0 0 NULL all null,empty, title_short NULL NULL NULL NULL 8 NULL YES varchar 50 NULL NULL 0 NULL NULL 70 0 0 0 NULL all null,empty, title_long NULL NULL NULL NULL 9 NULL YES varchar 50 NULL NULL 0 NULL NULL 70 0 0 0 NULL all null,empty, firstname NULL NULL NULL NULL 10 NULL YES varchar 50 NULL NULL 0 NULL NULL 70 0 0 0 NULL all null,empty, lastname NULL NULL NULL NULL 11 NULL YES varchar 50 NULL NULL 0 NULL NULL 70 0 0 0 NULL all null,empty, address NULL NULL NULL NULL 12 NULL YES varchar 100 NULL NULL 0 NULL NULL 70 0 0 0 NULL all null,empty, pc NULL NULL NULL NULL 13 NULL YES varchar 10 NULL NULL 0 NULL NULL 70 0 0 0 NULL all null,empty, kixcode NULL NULL NULL NULL 14 NULL YES varchar 20 NULL NULL 0 NULL NULL 70 0 0 0 NULL all null,empty, date_created NULL NULL NULL NULL 15 (getdate()) NO datetime NULL NULL NULL 1 19 19 0 0 0 70 Many (70) unique,same length, created_by NULL NULL NULL NULL 16 (user_name()) NO varchar 50 NULL NULL 0 13 13 0 0 0 1 loyalz-public same length, id_location_created NULL FOREIGN KEY location id_location 17 NULL YES int NULL 10 10 0 1 1 0 0 70 2 1,2 same length,all numeric, id_individual_type NULL FOREIGN KEY individual_type id_individual_type 18 NULL YES int NULL 10 10 0 NULL NULL 70 0 0 0 NULL all null,empty, optin NULL NULL NULL NULL 19 NULL YES int NULL 10 10 0 1 1 39 0 31 2 0,1 same length,

这可能是一个具有挑战性的错误。以下是AmirCharania在https://connect.microsoft.com/SQLServer/feedback/details/339410/网站上发表的评论。

我调整了AmirCharania给出的答案，用于选择实际表中的数据，而不是临时表。首先选择你的数据集到一个开发表中，然后运行以下命令:

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)

SQL Server 2019将最终返回更有意义的错误消息。

二进制或字符串数据将被截断=>错误消息增强 如果您(在生产环境中)出现了该错误，则不太容易看到该错误来自哪一列或哪一行，以及如何准确定位它。

要启用新行为，需要使用DBCC TRACEON(460)。sys.messages中的新错误文本:

SELECT * FROM sys.messages WHERE message_id = 2628

2628 -字符串或二进制数据将在表' %中被截断。*ls '，列' %.*ls '。截断值:' %.*ls '。

字符串或二进制数据将被截断:替换臭名昭著的错误8152

这个新消息也会被反向移植到SQL Server 2017 CU12(以及即将发布的SQL Server 2016 SP2 CU)，但不是默认情况下。您需要启用跟踪标志460，以便在会话级别或服务器级别将消息ID 8152替换为2628。 请注意，目前即使在SQL Server 2019 CTP 2.0中也需要启用相同的跟踪标志460。在未来的SQL Server 2019版本中，默认情况下，消息2628将取代消息8152。

SQL Server 2017 CU12也支持该特性。

改进:在SQL Server 2017中用扩展信息替换“字符串或二进制数据将被截断”消息

此SQL Server 2017更新引入了一条可选消息，其中包含以下附加上下文信息。 Msg 2628，级别16，状态6，程序程序命名，行Linenumber 字符串或二进制数据将在表'%中被截断。*ls'，列'%.*ls'。 截断值:'%.*ls'。 新的消息ID是2628。如果启用了跟踪标志460，此消息将替换任何错误输出中的消息8152。

db < > fiddle演示

ALTER DATABASE SCOPED CONFIGURATION VERBOSE_TRUNCATION_WARNINGS = { ON | OFF } APPLIES TO: SQL Server (Starting with SQL Server 2019 (15.x)) and Azure SQL Database Allows you to enable or disable the new String or binary data would be truncated error message. SQL Server 2019 (15.x) introduces a new, more specific error message (2628) for this scenario: String or binary data would be truncated in table '%.*ls', column'%.*ls'. Truncated value: '%.*ls'. When set to ON under database compatibility level 150, truncation errors raise the new error message 2628 to provide more context and simplify the troubleshooting process. When set to OFF under database compatibility level 150, truncation errors raise the previous error message 8152. For database compatibility level 140 or lower, error message 2628 remains an opt-in error message that requires trace flag 460 to be enabled, and this database scoped configuration has no effect.

我也遇到过类似的问题。我将数据从一个表复制到一个完全相同的表，除了名称。

最后，我使用SELECT into语句将源表转储到临时表中。

SELECT *
INTO TEMP_TABLE
FROM SOURCE_TABLE;

我比较了源表和临时表的模式。我发现其中一列是varchar(4000)，而我期待的是varchar(250)。

更新: 如果你感兴趣，varchar(4000)问题可以在这里解释:

对于Nvarchar(Max)，我只能在TSQL中获得4000个字符?

希望这能有所帮助。