Delimiter //
/**
 * Suppression de toutes les procédures en base
 *  doit être créer par l'utilisateur ROOT  pour fonctionner
 */

Drop Procedure if exists CASH_DROP_ALL_PROCEDURES//

Create Procedure CASH_DROP_ALL_PROCEDURES()
Begin

-- 	dropallProcMain: begin
-- 		Declare procName varchar(100);
-- 		Declare procCursor Cursor For
-- 			Select R.SPECIFIC_NAME
-- 			From information_schema.ROUTINES R
-- 			Where R.ROUTINE_TYPE = "PROCEDURE"
-- 				AND R.ROUTINE_SCHEMA = schema()
-- 				AND R.SPECIFIC_NAME Not In ('CASH_DROP_ALL_PROCEDURES')
-- 			;
-- 		Open procCursor;
-- 		dropallProcBlk: begin
-- 			Declare Exit Handler for NOT FOUND 
--                 Begin
--                     Close procCursor;
--                 End ;
-- 			dropallProcLoop: loop
-- 				fetch procCursor into procName;
-- 				Set @query = Concat('DROP PROCEDURE IF EXISTS '
-- 									, schema(), '.', procName
-- 									, ';'
-- 								)
-- 							;
-- -- 				Select @query;
-- 				PREPARE procDropStmt FROM @query; 
-- 				execute procDropStmt 
-- 					;
-- 				Deallocate prepare procDropStmt; 	
-- 			end loop dropallProcLoop;
-- 		End;
-- 	end;
-- requete de vérification
-- select SPECIFIC_NAME from information_schema.ROUTINES where ROUTINE_TYPE = 'PROCEDURE' and ROUTINE_SCHEMA = schema();

    Set Session group_concat_max_len = 100000000;

    Set @filename = Concat('/tmp/sql-drop-procedures.', (NOW() + 0 ), '.sql');
--     Set @filename = Concat('/tmp/sql-drop-procedures.sql');

    Set @query = Concat('
        Select
            CONCAT(
                "DROP PROCEDURE IF EXISTS "
				, schema(), ''.''
                , R.SPECIFIC_NAME

            )
        From 
            information_schema.ROUTINES AS R
        Where 
            R.ROUTINE_TYPE = "PROCEDURE"
            AND R.ROUTINE_SCHEMA = schema()
            AND R.SPECIFIC_NAME Not in (''CASH_DROP_ALL_PROCEDURES'')
        
        Into Outfile "', @filename,'"
        Fields
            Terminated By " "
            Optionally Enclosed By " "
			escaped by " "
        Lines
            Terminated By ";"
        ');
-- 	select @query;
    Prepare stmt From @query;
    Execute stmt;	
    Deallocate prepare stmt;


--     source @filename;
    select Concat('source ', @filename, ';') ;

end//

Delimiter ;