Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.

Search text in procedures

BubikolRamios's profile image BubikolRamios posted 4 years ago in Feature discussion Permalink

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's profile image ansgar posted 4 years ago Permalink

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's profile image BubikolRamios posted 4 years ago Permalink

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.

Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.
ansgar's profile image ansgar posted 4 years ago Permalink

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

Code modification/commit from ansgarbecker, 4 years ago, revision 5044
Support procedures and functions in "Find text on server" dialog. MySQL syntax only yet, others are to implement. See http://www.heidisql.com/forum.php?t=20456
ansgar's profile image ansgar posted 4 years ago Permalink

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's profile image BubikolRamios posted 4 years ago Permalink

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's profile image BubikolRamios posted 4 years ago Permalink
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's profile image BubikolRamios posted 4 years ago Permalink

I did not mention: Thanks. Great (-:

BubikolRamios's profile image BubikolRamios posted 4 years ago Permalink
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%';

Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.
BubikolRamios's profile image BubikolRamios posted 4 years ago Permalink

'information_schema.'

Code modification/commit from ansgarbecker, 4 years ago, revision 5055
Do not check existence of IS.routines before querying it. See http://www.heidisql.com/forum.php?t=20456#p20487
ansgar's profile image ansgar posted 4 years ago Permalink

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's profile image BubikolRamios posted 3 years ago Permalink

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's profile image BubikolRamios posted 3 years ago Permalink

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

BubikolRamios's profile image BubikolRamios posted 3 years ago Permalink

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.