PgSql查询字符串在哪些表哪些列存在

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( %张三% ) ;

— 查询字符串在哪些表，那些列存在

— 快速根据页面上显示的文字字符串定位到数据库的哪些关联表中– pgsql中查询proc或者 func 的内部代码的方式：– SELECT p.proname AS procedure_name, l.lanname AS language_name, p.prosrc AS source_code —source_code就是内部代码– FROM pg_proc p– JOIN pg_language l ON p.prolang = l.oid– WHERE l.lanname = plpgsql — 如果存储过程是使用 PL/pgSQL 编写的– AND p.proname = 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;