Home 2015 August Find any text into Stored Procedure and Function of MySQL

Find any text into Stored Procedure and Function of MySQL

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

In this post, I am sharing a script to find any text in Stored Procedure and Function of MySQL.

Many times we need to find any object name, column name or comment text in MySQL Routines.

Below is a script to achieve this:

Anvesh Patel
Determine free and occupied size of Tables and Database into MySQLFind Table Dependency in MySQL
Comments: 1
  1. Osvaldo
    August 25, 2019 at 5:31 am

    CREATE DEFINER=`root`@`localhost` PROCEDURE `dependencias`()
    NO SQL
    BLOCK1:BEGIN

    DECLARE bas, c1 VARCHAR(50);
    DECLARE fin int DEFAULT FALSE;
    DECLARE cur1 CURSOR FOR SELECT specific_name from information_schema.routines where routine_schema= bas;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET fin = TRUE;

    SET bas=’bayes’;

    DROP TABLE IF EXISTS dependencias;
    CREATE TABLE dependencias (a varchar(50), b varchar(50));
    OPEN cur1;
    rulo1: LOOP
    FETCH cur1 INTO c1;
    IF fin THEN
    LEAVE rulo1;
    END IF;
    BLOCK2: BEGIN
    DECLARE can int;
    DECLARE c2,cad varchar(50);
    DECLARE fin int DEFAULT FALSE;
    DECLARE cur2 CURSOR FOR SELECT specific_name from information_schema.routines where routine_schema= bas;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET fin = TRUE;
    OPEN cur2;
    rulo2: LOOP
    FETCH cur2 INTO c2;
    IF fin THEN
    LEAVE rulo2;
    END IF;
    SET cad=CONCAT (‘%’,c2,'(%’);
    SET can=(SELECT COUNT(*) FROM information_schema.routines WHERE Routine_Name = c1 AND routines.routine_definition LIKE cad AND Routine_Schema = bas);
    IF c1!=c2 AND can>0 THEN
    INSERT INTO `dependencias` values (c1,c2);
    END IF;
    END LOOP rulo2;
    CLOSE cur2;
    END BLOCK2;
    END LOOP rulo1;
    CLOSE cur1;
    END BLOCK1

Anvesh Patel
Anvesh Patel

Database Engineer

August 14, 2015 MySQL, MySQL DBA Script, , , , , , , , , , ,