How to see Mysql Routine information

How can we see all the routines inside our Mysql database. In Mysql functions, procedures can are called sorted routines. To query the information of such you can either use the SHOW command: 

mysql SHOW PROCEDURE STATUSG
*************************** 1. row ***************************
           Db: BXXX_234
         Name: c333
         Type: PROCEDURE
      Definer: root@localhost
     Modified: 2010-03-18 13:04:34
      Created: 2010-03-18 13:04:34
Security_type: DEFINER
      Comment:
*************************** 2. row ***************************
           Db: BXXX_234
         Name: m222
         Type: PROCEDURE
      Definer: root@localhost
     Modified: 2010-03-18 13:04:34
      Created: 2010-03-18 13:04:34
Security_type: DEFINER
      Comment:
*************************** 3. row ***************************
           Db: BXXX_234
         Name: P321
         Type: PROCEDURE
      Definer: root@localhost
     Modified: 2010-03-18 13:04:34
      Created: 2010-03-18 13:04:34
Security_type: DEFINER
      Comment:
*************************** 4. row ***************************
           Db: BXXX_234
         Name: produtos123
         Type: PROCEDURE
      Definer: root@localhost
     Modified: 2010-03-18 13:04:34
      Created: 2010-03-18 13:04:34
Security_type: DEFINER
      Comment:
Or you can use the syntax ot get the definition for a single routine: 
mysql SHOW PROCEDURE STATUS like 'remove%'G
*************************** 1. row ***************************
           Db: BASE_XXX
         Name: remove_garbage
         Type: PROCEDURE
      Definer: root@localhost
     Modified: 2010-03-18 13:04:34
      Created: 2010-03-18 13:04:34
Security_type: DEFINER
      Comment:
1 row in set (0.06 sec)
Another way for you to list the rotines is to query the INFORMATION_SCHEMA.ROUTINES table You will get more info on the rotine you want, such as it's type and it's definition. 
 mysql select * from INFORMATION_SCHEMA.ROUTINESG
*************************** 1. row ***************************
     SPECIFIC_NAME: cria_123
   ROUTINE_CATALOG: NULL
    ROUTINE_SCHEMA: BASE_xxx
      ROUTINE_NAME: c123
      ROUTINE_TYPE: PROCEDURE
    DTD_IDENTIFIER: NULL
      ROUTINE_BODY: SQL
ROUTINE_DEFINITION: BEGIN
    IF (EXISTS(SELECT 1 FROM pedidos WHERE id=pedidoid AND codigo_pedido IS NULL LIMIT 1)) THEN
        SET autocommit=0;
        IF(EXISTS(SELECT 1 FROM csras WHERE pedidos_id=pedidoid) OR
            EXISTS(SELECT 1 FROM csras WHERE id=pedidoid)) THEN
            DELETE FROM csras where id=pedidoid;
            DELETE FROM csras WHERE pedidos_id=pedidoid;
        END IF;
        INSERT INTO csras SELECT * FROM pedidos_itens WHERE pedidos_id=pedidoid;
        INSERT INTO csras SELECT * FROM pedidos WHERE id=pedidoid;
        COMMIT;
        if (EXISTS(SELECT 1 FROM csras WHERE id=pedidoid LIMIT 1)) THEN
            DELETE FROM pedidos_historico WHERE pedidos_id=pedidoid;
            DELETE FROM pedidos_itens WHERE pedidos_id=pedidoid;
            DELETE FROM modulos_pagamento_dados WHERE pedidos_id=pedidoid;
            DELETE FROM pedidos WHERE id=pedidoid;
            COMMIT;
        END IF;
    END IF;
    END
     EXTERNAL_NAME: NULL
 EXTERNAL_LANGUAGE: NULL
   PARAMETER_STYLE: SQL
  IS_DETERMINISTIC: YES
   SQL_DATA_ACCESS: CONTAINS SQL
          SQL_PATH: NULL
     SECURITY_TYPE: DEFINER
           CREATED: 2010-03-18 13:04:34
      LAST_ALTERED: 2010-03-18 13:04:34
          SQL_MODE: NO_AUTO_VALUE_ON_ZERO
   ROUTINE_COMMENT:

Copyright 2020. All Rights Reserved