Display all the UDFs installed on a MySQL server?

Question

How do I display all the UDFs (User Definied Functions) on a MySQL server? (version 5.0.x)

UPDATE I don't want to display the code of all the UDFs, I just want to list the name of each UDF that is installed. Sorta like SHOW DATABASES, but for UDFs.

Answer 1

UDF are stored in FUNC table of mysql DB.

Try this:

 select * from mysql.func;

Answer 2

All procedures are stored in the mysql db. I usually use this query when I'm looking for something.

select db,name,definer,modified from mysql.proc;

Answer 3

According to the docs it looks like you can't do this without debugging enabled in the build.

See: (This page is for functions as well as procs)

http://dev.mysql.com/doc/refman/5.0/en/show-procedure-code.html

Answer 4

If you want to build statements (say for restoring from a backup, or creating the same UDFs on another system) you can use this

select concat('create function`',`name`,'`returns ',if(`ret`=0,'string','integer'),' soname\'',`dl`,'\';')from mysql.func;

Should return something like

create function`preg_position`returns integer soname'lib_mysqludf_preg.so';
create function`preg_rlike`returns integer soname'lib_mysqludf_preg.so';
create function`preg_replace`returns string soname'lib_mysqludf_preg.so';
create function`lib_mysqludf_preg_info`returns string soname'lib_mysqludf_preg.so';
create function`preg_capture`returns string soname'lib_mysqludf_preg.so';
create function`preg_check`returns integer soname'lib_mysqludf_preg.so';
create function`unhtml`returns string soname'unhtml.so';