I need to get the user defined PROCEDURE / FUNCTION full definition in MySQL 8.
MySQL 5.6 contain mysql.proc table there we can get the function full definition but MySQL 8 does not contain the proc table.
But MySQL 5.6 and 8 also have information_schema.ROUTINES table it contain only the function body,
I can not find functions parameter and return types.
what I did in MySQL 5.6
SELECT
`name` AS _schema,
CONVERT(body USING utf8) AS _schemaDef,
CONVERT(param_list USING utf8) AS param_list,
CONVERT(`returns` USING utf8) AS `returns`
FROM mysql.proc
WHERE db = 'my_db_name'
Any idea how to do this MySQL 8?
My ultimate target is copying all function and procedure from one DB to another DB by using PHP
I have find a solution that...
SHOW CREATE FUNCTION function_name
Above query work fine in MySQL 5.6 and 8. But first I have to get list of FUNCTION names from information_schema.ROUTINES table and loop the record set, Inside the loop I have to run the above SHOW CREATE FUNCTION query.
How to Optimize this flow?
