在Tableau/Aginity中查询Redshift全库含"Certified Service"的表的SQL方法

以下查询完全适配2019年至今的所有Redshift正式版本，不需要复杂调整即可在Aginity、Tableau的自定义SQL窗口运行，解决老代码语法报错、版本规则过旧的问题。

先匹配对应的检索场景 #

多数网络流传的旧代码混淆了两类检索需求，是触发语法错误的核心原因，可根据实际目标选择对应查询：

• 场景1：检索表名本身包含"Certified Service"字符串的库对象
• 场景2：检索表内实际存储的数据中，存在"Certified Service"字符串的表

场景1：表名匹配的直接运行代码 #

无额外参数调整，粘贴即可运行：

SELECT 
  table_schema,
  table_name,
  table_type
FROM information_schema.tables
WHERE 
  -- 自动排除Redshift系统内置schema，避免无意义检索
  table_schema NOT IN ('pg_catalog', 'information_schema', 'pg_internal')
  -- 不区分大小写匹配目标字符串，需严格区分大小写可将ILIKE替换为LIKE
  AND table_name ILIKE '%Certified Service%';

场景2：表内数据匹配的低错误率代码 #

旧版全库数据检索代码报错，通常是因为没有过滤不支持字符串匹配的列类型、没有处理带特殊字符的表名/列名的引号转义。以下代码自动规避了上述问题：

1. 先运行以下语句，自动生成所有需要执行的检索片段

   SELECT 
     'SELECT ''' || table_schema || '.' || table_name || ''' AS matched_table, '
     || quote_ident(column_name) || ' AS matched_column '
     || 'FROM ' || quote_ident(table_schema) || '.' || quote_ident(table_name)
     -- 统一转文本类型匹配，兼容super、自定义类型的字符串检索，避免长度限制报错
     || ' WHERE ' || quote_ident(column_name) || '::TEXT ILIKE ''%Certified Service%'' LIMIT 1;' AS generated_sql
   FROM information_schema.columns
   WHERE 
     -- 排除系统schema
     table_schema NOT IN ('pg_catalog', 'information_schema', 'pg_internal')
     -- 仅选可转文本的列类型，避免整数、时间戳、空间几何类列触发语法错误
     AND data_type IN ('character varying', 'character', 'text', 'super', 'bpchar')
   ORDER BY table_schema, table_name;
   

2. 新手操作说明
   • 上述代码本身不会扫描全表业务数据，仅会生成逐表的检索语句，不需要手动修改语句内容
   • 将结果集中generated_sql列的所有语句用UNION ALL拼接后一次运行，即可拿到所有存储了"Certified Service"字符串的表名、对应匹配列
   • 后续需要替换检索的目标字符串时，仅需修改代码中%Certified Service%的内容即可，其余逻辑无需调整

新手操作注意事项 #

• 确保连接Redshift使用的账号，对要检索的业务schema有至少表的SELECT权限，否则结果会漏掉无权限访问的表
• 不要将pg_internal这类Redshift内部系统schema加入检索范围，否则会大幅拉长查询运行时间，且无实际业务价值
• Tableau环境下运行时，需关闭平台针对Redshift旧版本的自动SQL改写规则，避免平台自行修改语句触发报错

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