数据库查看存储过程和函数

• mysql示例代码

-- 1. 查看存储过程
SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE' and ROUTINE_SCHEMA = 'your_database_name' ;

-- 2. 查看函数
SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'FUNCTION' and ROUTINE_SCHEMA = 'your_database_name' ;

-- 3.查看存储过程和函数
SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE in ('PROCEDURE','FUNCTION')  and ROUTINE_SCHEMA = 'your_database_name' ;

• postgresql示例代码

-- 1. 查看存储过程
SELECT proname, prosrc
FROM pg_catalog.pg_proc
WHERE prorettype = 'pg_catalog.void'::pg_catalog.regtype
and proname <> prosrc;

-- 2. 查看函数
SELECT proname, prosrc
FROM pg_catalog.pg_proc
WHERE prorettype <> 'pg_catalog.void'::pg_catalog.regtype
and proname <> prosrc;

-- 3.查看存储过程和函数
SELECT proname, prosrc
FROM pg_catalog.pg_proc
WHERE proname <> prosrc;

• sql server示例代码

SELECT specific_name, routine_definition
FROM information_schema.routines
WHERE routine_type = 'PROCEDURE'

-- 2. 查看函数
SELECT specific_name, routine_definition
FROM information_schema.routines
WHERE routine_type = 'FUNCTION'

-- 3.查看存储过程和函数
SELECT specific_name, routine_definition
FROM information_schema.routines
WHERE routine_type in ('PROCEDURE' , 'FUNCTION');