查找仅包含指定值集合的数据库表与列

核心判断逻辑（单表列） #

要确认某列仅包含(1, 6, 18, 20, 29, 33, 35, 36, 38)这些值，核心是检查该列不存在任何不在此集合中的非空值。对应的WHERE子句片段为：

your_column NOT IN (1, 6, 18, 20, 29, 33, 35, 36, 38)
AND your_column IS NOT NULL

如果针对某表列执行SELECT 1 FROM your_table WHERE 上述条件返回0行，说明该列符合要求。

批量遍历所有表列的动态SQL方案 #

由于数据库有4000多张表，手动检查不现实，需用动态SQL自动生成检查脚本。以下以SQL Server为例（其他数据库可调整语法）：

方案1：打印符合条件的表列 #

DECLARE @allowedValues NVARCHAR(100) = '1,6,18,20,29,33,35,36,38'
DECLARE @sql NVARCHAR(MAX) = ''

-- 生成所有数值类型列的检查语句
SELECT @sql = @sql + '
IF NOT EXISTS (
    SELECT 1
    FROM ' + QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) + '
    WHERE ' + QUOTENAME(COLUMN_NAME) + ' NOT IN (' + @allowedValues + ')
      AND ' + QUOTENAME(COLUMN_NAME) + ' IS NOT NULL
)
BEGIN
    PRINT ''' + QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) + '.' + QUOTENAME(COLUMN_NAME) + '''
END
'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE IN ('int', 'smallint', 'bigint', 'tinyint') -- 根据数据库支持的数值类型调整
  AND TABLE_SCHEMA = 'dbo' -- 替换为你的目标 schema

-- 执行生成的脚本
EXEC sp_executesql @sql

方案2：将结果存入临时表（便于后续分析） #

CREATE TABLE #MatchingColumns (
    SchemaName NVARCHAR(128),
    TableName NVARCHAR(128),
    ColumnName NVARCHAR(128)
)

DECLARE @allowedValues NVARCHAR(100) = '1,6,18,20,29,33,35,36,38'
DECLARE @sql NVARCHAR(MAX) = ''

SELECT @sql = @sql + '
IF NOT EXISTS (
    SELECT 1
    FROM ' + QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) + '
    WHERE ' + QUOTENAME(COLUMN_NAME) + ' NOT IN (' + @allowedValues + ')
      AND ' + QUOTENAME(COLUMN_NAME) + ' IS NOT NULL
)
BEGIN
    INSERT INTO #MatchingColumns (SchemaName, TableName, ColumnName)
    VALUES (''' + TABLE_SCHEMA + ''', ''' + TABLE_NAME + ''', ''' + COLUMN_NAME + ''')
END
'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE IN ('int', 'smallint', 'bigint', 'tinyint')
  AND TABLE_SCHEMA = 'dbo'

EXEC sp_executesql @sql

-- 查询结果
SELECT * FROM #MatchingColumns
DROP TABLE #MatchingColumns

实用优化建议 #

• 过滤空表：跳过无数据的表以节省时间，可在生成脚本时加入表行数判断（如SQL Server关联sys.tables并添加t.rows > 0条件）。
• 处理空值：若业务允许列包含NULL，删除WHERE子句中的AND your_column IS NOT NULL；若不允许NULL，可额外检查列是否存在NULL值。
• 分批次执行：4000多张表一次性执行可能耗时，可按表名前缀分段生成脚本（如WHERE TABLE_NAME LIKE 'A%'）。
• 临时索引优化：对于大表，可临时给待检查列创建索引，完成后删除，提升检查速度。

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