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 ;

As running multiple INSERT statements is usually not the best way of pushing large amounts of data into a database, you might want to use various database-specific SQL extensions in order to achieve the goal. Here’s what I do on MySQL and SQL Server:

MySQL

INSERT INTO table (field1, field2)
VALUES (value1, value2), (another_value1, another_value2), [and so on];

PostgreSQL

PREPARE PgSQLInserter (int, int) AS
INSERT INTO table (field_1, field_2) VALUES ($1, $2);
EXECUTE PgSQLInserter (1, 234);
EXECUTE PgSQLInserter (2, 5678);
EXECUTE PgSQLInserter (3, 234);
DEALLOCATE PgSQLInserter;

SQL Server:

INSERT INTO table (field1, field2)
SELECT (value1, value2) UNION
SELECT (value1, value2) UNION
SELECT (value1, value2) UNION [and so on];