Get DDL of Procedure and function in Mysql / MariaDB database with MYSQLDUMP utility

Example: –routines is used to include procedure and function in MYSQLDUMP backup

Following are used to generate script for procedure or function:
–routines is used to add procedure and function
–skip-triggers is used to avoid triggers DDL by default it true
–no-create-info is used to avoid creating table statements
–no-data is used to avoid data or table connects
–no-create-DB is used to avoid creating database statements.
–skip-opt is used to avoid like Shorthand for –add-drop-table –add-locks –create-options –disable-keys –extended-insert –lock-tables –quick –set-charset

mysqldump -u root -p --routines --no-create-info --skip-triggers --no-data --no-create-db --skip-opt test > location_of_file

C:\WINDOWS\system32>mysqldump -u root -p --routines --no-create-info --skip-triggers --no-data --no-create-db --skip-opt test > c:\d\outputfile.sql
Enter password: ********

Output:
-- MariaDB dump 10.19  Distrib 10.6.5-MariaDB, for Win64 (AMD64)
--
-- Host: localhost    Database: test
-- ------------------------------------------------------
-- Server version	10.6.5-MariaDB

DELIMITER ;;
CREATE DEFINER=`root`@`localhost` FUNCTION `sf_escapechars`(p_text varchar(255)) RETURNS varchar(255) CHARSET latin1
begin
return REPLACE(REPLACE(REPLACE(p_text,"\'","\\\'"),"_","\_"),"%","\%");
end ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = cp850 */ ;
/*!50003 SET character_set_results = cp850 */ ;
/*!50003 SET collation_connection  = cp850_general_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `myprocedure`(INOUT p_text varchar(255))
begin
set p_text = concat('"',p_text,'"');
select QUOTE(p_text) into p_text from dual;
select * from test where name = p_text;
end ;;
DELIMITER ;

For include Drop command we have to remove –skip-opts

C:\WINDOWS\system32>mysqldump -u root -p --routines --replace --no-create-info --skip-triggers --no-data --no-create-db  test > c:\d\outputfile.sql
Enter password: ********

Output:
-- MariaDB dump 10.19  Distrib 10.6.5-MariaDB, for Win64 (AMD64)
--
-- Host: localhost    Database: test
-- ------------------------------------------------------
-- Server version	10.6.5-MariaDB

--
-- Dumping routines for database 'test'
--
/*!50003 DROP FUNCTION IF EXISTS `sf_escapechars` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = cp850 */ ;
/*!50003 SET character_set_results = cp850 */ ;
/*!50003 SET collation_connection  = cp850_general_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` FUNCTION `sf_escapechars`(p_text varchar(255)) RETURNS varchar(255) CHARSET latin1
begin
return REPLACE(REPLACE(REPLACE(p_text,"\'","\\\'"),"_","\_"),"%","\%");
end ;;
DELIMITER ;

Error: if we want to replace the exiting procedure with dumps file then we need to use drop statements before the create otherwise we get the error

C:\WINDOWS\system32>mysql -u root -p classicmodels < c:\d\outputfile.sql
Enter password: ********
ERROR 1304 (42000) at line 24: FUNCTION sf_escapechars already exists

Note: To Overcome we use the second command option to include the DROP statements

-- After removing --skip-opts include DROP statements
C:\WINDOWS\system32>mysqldump -u root -p --routines --replace --no-create-info --skip-triggers --no-data --no-create-db  test > c:\d\outputfile.sql
Enter password: ********

-- Run again will not get the function already exists error
C:\WINDOWS\system32>mysql -u root -p classicmodels < c:\d\outputfile.sql
Enter password: ********