feature request: `create procedure` for mysql

[croconut]

not sure how useful this would be for other people - i mostly want to be able to pull procedure definitions apart, inspect what tables / views they require defined.

[brainsiq]

Related: drop procedure is also not supported for mysql but is for other dialects.

[urvil5256]

Hi,

To identify what tables and views are used inside the stored procedure, Below is the query which will provide you a list of tables or views are used:

SELECT dep.objid::regprocedure AS procedure, ref.objid::regclass AS dependent_on FROM pg_depend dep JOIN pg_depend ref ON dep.objid = ref.objid WHERE dep.classid = 'pg_proc'::regclass::oid -- Procedures AND ref.classid = 'pg_class'::regclass::oid -- Tables/Views -- Add filters for specific procedures if needed ;

Also, to find views depending on tables (analogous):

SELECT DISTINCT v.oid::regclass AS view FROM pg_depend AS d JOIN pg_rewrite AS r ON r.oid = d.objid JOIN pg_class AS v ON v.oid = r.ev_class WHERE v.relkind = 'v' -- views only AND d.refobjid = 'your_table_name'::regclass;

Please check by running this query and let me know its help full for you or not?
Thanks

[croconut]

I'm on MySQL, so that specifically wouldn't be lol. I did end up switching to selecting from the information schema for most things tho

[croconut]

this ticket is closable for me, just never came back to it

[croconut]

I was initially thinking I'd want to inspect this before running the create, but it turns out I didn't need to