Chris’s cheat sheets

Here’s what I do to be able to easily view views and triggers in the current database in MySQL command line program. It’s quicker than running select queries against the information_schema table and it only pulls the information I need. The internal MySQL “SHOW TRIGGERS” command spits out the actual trigger body making it pretty inconvenient if you just want a simple trigger names listing.

Here’s what you do:

– Universal triggers viewer using a stored procedure. Usage: CALL triggers();

DELIMITER |

CREATE PROCEDURE triggers ()
BEGIN
SELECT TRIGGER_NAME, DEFINER, EVENT_MANIPULATION,
EVENT_OBJECT_TABLE, CREATED
FROM information_schema.triggers
WHERE trigger_schema = database();
END;
|

– Universal views viewer using a procedure. Usage: CALL views();
CREATE PROCEDURE views ()
BEGIN
SELECT table_name, definer, is_updatable
FROM information_schema.views
WHERE table_schema = database();
END;
|

DELIMITER ;