distal-attribute
distal-attribute
distal-attribute
distal-attribute

Search text in procedures

BubikolRamios posted 1 year ago in Feature discussion

Bringing this up ....

tree style options/ group objects by type

this nicely reflects in CTL+SHIFT-F

You can select Procedures only

Sadly it does not work on procedures.

If there is no other way to do it, you could probably sql dump procedures only and then perform search there ?

ansgar posted 1 year ago

Yes, HeidiSQL can only search in table rows, so it says:

Skipped. A procedure does not contain rows.

But you want to search for some SQL text inside the procedure body? I guess you can accomplish that by querying IS:

SELECT * FROM `information_schema`.`ROUTINES`
WHERE `ROUTINE_DEFINITION` LIKE '%searchtext%';
BubikolRamios posted 1 year ago

This is great.Much easier than I thought. Could you put that behind your 'Search text' dialog ? Then "Skipped" would not exist any more.

I would then make that (group by object type) tree style option, default here, as on attached image, regardless of what kind of view is selected inside normal tree.

Would be happy, even with onyl your upper sql (& appropriate for triggers,.. ), if checked, would auto open NEW query window (one only as multiple queries inside one window works great now) & execute.

ansgar posted 1 year ago

I will see if I can fire that IS query on procedures and triggers instead of saying "skipped".

ansgar posted 1 year ago

r5044 now searches the text to find in the definition body of procedures and functions. HeidiSQL also displays the number of occurrences in the "Found rows" column.

MySQL preferred here. MSSQL and PostgreSQL will most probably barf with some wrong SQL syntax, until I put in some version conditional.

BubikolRamios posted 1 year ago

Some observations:

1.SKIPPED - information_schema.routines missing. Reason - I normaly don't have in connection settings to open information_schema, dont want to do any mess there by accident.

  1. Relavance allways 0, don't know what that mean, doh found rows are from 0 to x per procedure/function

  2. To this become useful, there should be right click menu/ open "selected" function/procedure
BubikolRamios posted 1 year ago
To this become useful, there should be right click menu/ open "selected" function/procedure

or second right click menu option, "view it". Better, I see main usability , copying part of code into func/proc that I'm working on. Don't want to close the one in progress of development.

BubikolRamios posted 1 year ago

I did not mention: Thanks. Great (-:

BubikolRamios posted 1 year ago
SKIPPED - information_schema.routines missing.
Reason - I normaly don't have in connection settings to open information_schema, dont want to do any mess there by accident.

I guess you omited bold stuff here:

SELECT * FROM **information_schema**.ROUTINES WHERE ROUTINE_DEFINITION LIKE '%cur%';

BubikolRamios posted 1 year ago

'information_schema.'

ansgar posted 1 year ago

Searching text in routines now also works on PostgreSQL and MSSQL since r5054.

Checking existence of IS.routines removed in r5055, so you don't get that *information_schema.routines missing" message when you don't have IS in your session databases.

BubikolRamios posted 11 months ago

Ok checked that *information_schema.routines missing" works.

But please check search for text in stored routines/ functions. It finds all routines/functions regardles of text thta searches for. MySql.

Latest relaease.

BubikolRamios posted 11 months ago

Ahh, forget that. Was expecting to see only routines with hit in resultset, not the one with 0 hits.

BubikolRamios posted 9 months ago

Noted v 9.3.0.5014 it skips triggers. If not in latest version do please do it for triggers too.

Please login to leave a reply, or register at first.