SQL Server中如何定位表中特定值'balance'所在列？

方法1：生成单列检查语句（分步排查） #

执行以下SQL，生成针对每一列的检查语句：

SELECT 
    CONCAT(
        'SELECT ''', COLUMN_NAME, ''' AS ColumnName, COUNT(*) AS MatchCount FROM [DM].[X] WHERE ',
        CASE DATA_TYPE 
            WHEN 'int' THEN 'CAST(' + COLUMN_NAME + ' AS VARCHAR(50)) = ''balance'''
            ELSE COLUMN_NAME + ' = ''balance'''
        END
    ) AS CheckQuery
FROM INFORMATION_SCHEMA.columns 
WHERE table_schema = 'DM' 
  AND table_name = 'X'
  AND DATA_TYPE IN ('int', 'nvarchar', 'varchar');

将结果中每一行的CheckQuery单独执行，MatchCount > 0的列就是存在'balance'值的列。

方法2：生成联合查询语句（一次性排查所有列） #

执行以下SQL生成联合查询语句：

SELECT 
    STRING_AGG(
        CONCAT(
            'SELECT ''', COLUMN_NAME, ''' AS ColumnName, COUNT(*) AS MatchCount FROM [DM].[X] WHERE ',
            CASE DATA_TYPE 
                WHEN 'int' THEN 'CAST(' + COLUMN_NAME + ' AS VARCHAR(50)) = ''balance'''
                ELSE COLUMN_NAME + ' = ''balance'''
            END
        ),
        ' UNION ALL '
    ) AS FullCheckQuery
FROM INFORMATION_SCHEMA.columns 
WHERE table_schema = 'DM' 
  AND table_name = 'X'
  AND DATA_TYPE IN ('int', 'nvarchar', 'varchar');

复制生成的FullCheckQuery执行，即可一次性获取所有列的匹配结果，快速定位目标列。

方法3：直接查询目标列名 #

执行以下SQL，直接返回包含'balance'值的列名：

-- 排查int类型列（值为'balance'导致转换失败）
SELECT 
    COLUMN_NAME
FROM INFORMATION_SCHEMA.columns 
WHERE table_schema = 'DM' 
  AND table_name = 'X'
  AND DATA_TYPE = 'int'
  AND EXISTS (
      SELECT 1 FROM [DM].[X] 
      WHERE TRY_CAST(CAST(COLUMN_NAME AS VARCHAR(50)) AS int) IS NULL 
        AND CAST(COLUMN_NAME AS VARCHAR(50)) = 'balance'
  )
UNION ALL
-- 排查字符串类型列
SELECT 
    COLUMN_NAME
FROM INFORMATION_SCHEMA.columns 
WHERE table_schema = 'DM' 
  AND table_name = 'X'
  AND DATA_TYPE IN ('nvarchar', 'varchar')
  AND EXISTS (
      SELECT 1 FROM [DM].[X] 
      WHERE COLUMN_NAME = 'balance'
  );

原语句失效原因 #

你之前生成的语句使用WHERE 'balance' IN (col1, col2...)，当包含int类型列时，SQL引擎会自动尝试将字符串'balance'转换为int类型，直接触发Msg 245转换错误，导致语句无法执行。必须先将int列转换为字符串类型再比较，才能避免隐式转换引发的错误。

内容的提问来源于stack exchange，提问作者Natasha1305