Gerard's codebase: Listing MySQL functions and Procedures

Gerard's codebase

This is a personal repository of code snippets and information.

Over the years I have generated lots of little sub programs and stored away acres of useful code snippets. The problem is always to locate them.

Even more time wasting is forgetting how to do simple things when you havnt developed in an environment for a few years (or even a few months)

My new years resolution is to start putting them up in a common place as I produce them. (thanks google)

They are handy for me and, with a bit of a clean up and documentation, they might be handy for others if they wander in here.

Gerard 2008

Sunday, January 2, 2011

Listing MySQL functions and Procedures

Maintaining and updating stored procedures and functions in MySQL often will require root access which in practice means that you might need to have some interaction with your hosting provider if you wish to use them.

Operating the the standard phpMyAdmin interface gives no support for them other then the ability to create them through script.

The following bits of SQL are useful for viewing stored procedures and functions in a hosted database accessed through phpMyAdmin

SELECT ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE="FUNCTION"
AND ROUTINE_SCHEMA="database"

SELECT ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE="PROCEDURE"
AND ROUTINE_SCHEMA="database"

Gerard's codebase