DO $$
BEGIN
IF EXISTS (SELECT 1 FROM pg_catalog.pg_proc WHERE proname = 'sp_FindStringProcOrFunc') THEN
DROP PROCEDURE sp_FindStringProcOrFunc;
END IF;
END $$;
-- gzt-20240926
-- 这里是 pgsql数据库：可视化工具 自带 pgadmin 4 其他：navicat、dbeaver等等
-- 查询方式： call sp_FindStringProcOrFunc('%张三%') ;
-- 查询字符串在哪些表，那些列存在
-- 快速根据页面上显示的文字字符串定位到数据库的哪些关联表中
CREATE OR REPLACE PROCEDURE sp_FindStringProcOrFunc
(
old_string VARCHAR
) as
$$
BEGIN
DECLARE
tbname varchar(50);
colname varchar(500);
sql_stmt text;
j integer;
BEGIN
FOR tbname IN SELECT relname FROM pg_catalog.pg_class WHERE relkind = 'r' and relname in(
SELECT table_name
FROM information_schema.tables
WHERE table_schema NOT LIKE 'pg_%' -- 排除以 'pg_' 开头的模式，这些是系统模式[新建架构的时候是不允许架构名称pg_开头的，所以这里可以大胆排除掉]
AND table_schema <> 'information_schema' -- 排除 information_schema 模式
AND table_type = 'BASE TABLE' -- 只查询基本表，排除视图
and table_catalog = current_database() -- 只查询当前数据库的
and table_schema = current_schema() --- 只查询当前架构的
) LOOP
FOR colname IN SELECT attname FROM pg_catalog.pg_attribute WHERE attrelid = tbname::regclass AND attnum > 0 AND atttypid IN (
SELECT oid FROM pg_catalog.pg_type WHERE 1=1
-- and typname like '%name%' or typname like '%char%'
-- or typname like '%text%' or typname like '%str%' or typname like '%json%' or typname like '%xml%'
and typname in('name','text','json','xml','varchar','cstring')
) LOOP
IF colname = old_string THEN
RAISE NOTICE '包含有列的表 % 包含有列的表 %', colname, tbname;
END IF;
sql_stmt := 'SELECT COUNT(1) FROM ' || tbname || ' WHERE ' || colname || ' LIKE ''' || old_string || '''';
EXECUTE sql_stmt INTO j;
IF j > 0 THEN
RAISE NOTICE '包含字串的表名: %,包含字串的列名: %', tbname, colname;
sql_stmt := 'SELECT ' || colname || ', * FROM ' || tbname || ' WHERE ' || colname || ' LIKE ''' || old_string || '''';
RAISE NOTICE '%', sql_stmt;
END IF;
END LOOP;
END LOOP;
END;
END;
$$
LANGUAGE plpgsql;