SQL Server 2019中能否搜索全表排查小写字母存在情况？

下面分不同数据库类型给出具体实现方案：

MySQL/MariaDB #

生成查询语句（手动执行） #

先运行这段SQL，生成每个字符型字段的检查语句，再逐个执行生成的语句即可查看结果：

SELECT 
    CONCAT(
        'SELECT ''', table_name, '.' , column_name, ''' AS `字段`, ',
        'COUNT(*) AS `含小写字母的记录数` ',
        'FROM ', table_name, ' ',
        'WHERE ', column_name, ' REGEXP ''[a-z]'';'
    ) AS `查询语句`
FROM information_schema.columns
WHERE 
    table_schema = '你的数据库名'
    AND data_type IN ('char', 'varchar', 'text', 'mediumtext', 'longtext');

自动执行（存储过程） #

创建并调用存储过程，自动遍历所有表和字符字段，输出包含小写字母的记录数：

DELIMITER //
CREATE PROCEDURE CheckLowercaseInAllTables()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE tbl_name VARCHAR(255);
    DECLARE col_name VARCHAR(255);
    DECLARE cur CURSOR FOR 
        SELECT table_name, column_name 
        FROM information_schema.columns 
        WHERE table_schema = DATABASE() 
            AND data_type IN ('char', 'varchar', 'text', 'mediumtext', 'longtext');
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO tbl_name, col_name;
        IF done THEN
            LEAVE read_loop;
        END IF;
        SET @sql = CONCAT(
            'SELECT ''', tbl_name, '.' , col_name, ''' AS `字段`, ',
            'COUNT(*) AS `含小写字母的记录数` ',
            'FROM ', tbl_name, ' ',
            'WHERE ', col_name, ' REGEXP ''[a-z]'''
        );
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END LOOP;
    CLOSE cur;
END //
DELIMITER ;

-- 调用存储过程
CALL CheckLowercaseInAllTables();

PostgreSQL #

生成查询语句（手动执行） #

运行以下SQL生成检查语句，再执行生成的结果：

SELECT 
    format(
        'SELECT ''%I.%I'' AS 字段, COUNT(*) AS 含小写字母的记录数 FROM %I WHERE %I ~ ''[a-z]'';',
        table_name, column_name, table_name, column_name
    ) AS 查询语句
FROM information_schema.columns
WHERE 
    table_schema = 'public' -- 默认schema为public，按需替换
    AND data_type IN ('character', 'character varying', 'text');

自动执行（自定义函数） #

创建函数并调用，自动遍历检查：

CREATE OR REPLACE FUNCTION check_lowercase_all_tables()
RETURNS TABLE(field_name text, lowercase_count bigint) AS $$
DECLARE
    rec record;
BEGIN
    FOR rec IN 
        SELECT table_name, column_name 
        FROM information_schema.columns 
        WHERE table_schema = 'public'
            AND data_type IN ('character', 'character varying', 'text')
    LOOP
        RETURN QUERY EXECUTE format(
            'SELECT ''%I.%I''::text, COUNT(*) FROM %I WHERE %I ~ ''[a-z]''',
            rec.table_name, rec.column_name, rec.table_name, rec.column_name
        );
    END LOOP;
END;
$$ LANGUAGE plpgsql;

-- 调用函数
SELECT * FROM check_lowercase_all_tables();

SQL Server #

生成查询语句（手动执行） #

运行这段SQL生成检查语句，再逐个执行：

SELECT 
    'SELECT ''' + QUOTENAME(table_name) + '.' + QUOTENAME(column_name) + ''' AS 字段, COUNT(*) AS 含小写字母的记录数 FROM ' + QUOTENAME(table_name) + ' WHERE ' + QUOTENAME(column_name) + ' LIKE ''%[a-z]%'';' AS 查询语句
FROM information_schema.columns
WHERE 
    table_catalog = '你的数据库名'
    AND data_type IN ('char', 'varchar', 'text', 'nchar', 'nvarchar', 'ntext');

自动执行（存储过程） #

创建并调用存储过程自动检查：

CREATE PROCEDURE CheckLowercaseInAllTables
AS
BEGIN
    DECLARE @tbl_name NVARCHAR(255), @col_name NVARCHAR(255);
    DECLARE cur CURSOR FOR 
        SELECT table_name, column_name 
        FROM information_schema.columns 
        WHERE table_catalog = DB_NAME()
            AND data_type IN ('char', 'varchar', 'text', 'nchar', 'nvarchar', 'ntext');
    
    OPEN cur;
    FETCH NEXT FROM cur INTO @tbl_name, @col_name;
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
        DECLARE @sql NVARCHAR(MAX) = 
            'SELECT ''' + QUOTENAME(@tbl_name) + '.' + QUOTENAME(@col_name) + ''' AS 字段, COUNT(*) AS 含小写字母的记录数 FROM ' + QUOTENAME(@tbl_name) + ' WHERE ' + QUOTENAME(@col_name) + ' LIKE ''%[a-z]%''';
        EXEC sp_executesql @sql;
        FETCH NEXT FROM cur INTO @tbl_name, @col_name;
    END;
    
    CLOSE cur;
    DEALLOCATE cur;
END;

-- 调用存储过程
EXEC CheckLowercaseInAllTables;

注意事项 #

• 替换语句中的你的数据库名、schema名为实际环境对应的值
• 执行需要足够权限（读取系统表、执行动态SQL/存储过程的权限）
• 大表数据量较大时，建议在业务低峰期操作，避免影响性能

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