如何在Netezza中查找含特定值的表和列？（R/SQL方案）

一、纯Netezza SQL实现 #

可以通过动态SQL生成并执行检查语句，无需依赖外部工具：

1. 生成动态查询语句 #

先从information_schema.columns提取用户表的字符类型列信息，再生成每个列的匹配检查语句：

SELECT 
    'SELECT ''' || table_schema || ''' AS schema_name, ''' || table_name || ''' AS table_name, ''' || column_name || ''' AS column_name FROM ' || 
    table_schema || '.' || table_name || ' WHERE ' || column_name || ' LIKE ''%ABC%'' LIMIT 1' AS query
FROM information_schema.columns
WHERE 
    table_schema NOT IN ('INFORMATION_SCHEMA', '_V_MONITOR', '_V_SYS_MONITOR') -- 排除系统库
    AND data_type IN ('CHARACTER', 'CHARACTER VARYING', 'VARCHAR', 'TEXT') -- 仅检查字符型列，避免非字符列报错
ORDER BY table_schema, table_name, column_name;

2. 自动执行动态语句（需权限） #

如果有权限创建存储过程，可以写一个批量执行的存储过程：

CREATE OR REPLACE PROCEDURE find_columns_with_abc()
LANGUAGE NZPLSQL
AS BEGIN_PROC
DECLARE
    v_query TEXT;
    cur CURSOR FOR
        SELECT 
            'SELECT ''' || table_schema || ''' AS schema_name, ''' || table_name || ''' AS table_name, ''' || column_name || ''' AS column_name FROM ' || 
            table_schema || '.' || table_name || ' WHERE ' || column_name || ' LIKE ''%ABC%'' LIMIT 1' AS query
        FROM information_schema.columns
        WHERE 
            table_schema NOT IN ('INFORMATION_SCHEMA', '_V_MONITOR', '_V_SYS_MONITOR')
            AND data_type IN ('CHARACTER', 'CHARACTER VARYING', 'VARCHAR', 'TEXT');
BEGIN
    OPEN cur;
    LOOP
        FETCH cur INTO v_query;
        EXIT WHEN NOT FOUND;
        EXECUTE v_query;
    END LOOP;
    CLOSE cur;
END;
END_PROC;

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

注意：每个查询加LIMIT 1是为了提升效率——只要找到匹配值就停止查询，不用扫全表。

二、R结合SQL的优化实现 #

针对你之前遇到的变量替换和空值过滤问题，调整后的R代码如下：

library(DBI)
library(dplyr)
library(purrr)

# 连接Netezza（替换为你的DSN信息）
con <- dbConnect(odbc::odbc(), dsn = "你的Netezza数据源名")

# 获取用户表的字符型列信息
summary_df <- dbGetQuery(con, "
    SELECT table_schema, table_name, column_name
    FROM information_schema.columns
    WHERE 
        table_schema NOT IN ('INFORMATION_SCHEMA', '_V_MONITOR', '_V_SYS_MONITOR')
        AND data_type IN ('CHARACTER', 'CHARACTER VARYING', 'VARCHAR', 'TEXT')
")

# 定义单列检查函数
check_single_column <- function(schema, table, col) {
    query <- sprintf(
        "SELECT '%s' AS schema_name, '%s' AS table_name, '%s' AS column_name FROM %s.%s WHERE %s LIKE '%%ABC%%' LIMIT 1",
        schema, table, col, schema, table, col
    )
    # 捕获空结果，避免循环中断
    tryCatch(dbGetQuery(con, query), error = function(e) tibble())
}

# 批量执行并合并结果
match_results <- pmap_dfr(summary_df, check_single_column)

# 关闭连接
dbDisconnect(con)

# 查看最终结果
print(match_results)

关键优化点 #

• 用sprintf做变量替换，彻底避免字符串拼接错误
• 仅检查字符型列，跳过无法执行LIKE的数值/日期列
• 用tryCatch捕获查询错误，保证循环不会中断
• 用pmap_dfr自动合并所有非空结果，无需手动处理空值判断

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