Professional Web Applications Themes

Dropping Procedures - Accessign Procedure names - MySQL

Could someone tell me if it is possible to use a wildcard character in a drop procedure command? E.g., something like this: drop procedure if exists testdb.testproc?; that would drop testproc1 testproc2 testproc3 etc. ....or an alternative method to do this rather than delete a bunch with similar names individually? ....and where in the information_schema is the list of procedures located? Thanks Jeff -- Posted via a free Usenet account from http://www.teranews.com...

  1. #1

    Default Dropping Procedures - Accessign Procedure names


    Could someone tell me if it is possible to use a wildcard character in a
    drop procedure command?

    E.g., something like this:

    drop procedure if exists testdb.testproc?;

    that would drop testproc1 testproc2 testproc3 etc.

    ....or an alternative method to do this rather than delete a bunch with
    similar names individually?

    ....and where in the information_schema is the list of procedures located?

    Thanks

    Jeff



    --
    Posted via a free Usenet account from http://www.teranews.com

    Jeff Guest

  2. #2

    Default Re: Dropping Procedures - Accessign Procedure names

    On Thu, 12 Apr 2007 17:46:52 -0500, "Jeff" <com> wrote:
     

    Directly, it is unlikely.
     

    Use information_schema
     

    You can get all the routines names you want with :

    SELECT information_schema.ROUTINES.SPECIFIC_NAME
    FROM information_schema.ROUTINES
    WHERE information_schema.ROUTINES.SPECIFIC_NAME REGEXP '...';

    From there you can make out a procedure that will delete all the
    values found by this request, and this procedure is fed with the
    regexp you want.

    You know about cursors ? If you need i can tell you more, just ask.
    subtenante Guest

  3. #3

    Default Re: Dropping Procedures - Accessign Procedure names


    "subtenante" <com> wrote in message
    news:com... 


    The following works to select the procedure names ending in W1 that I wish
    to delete.
    I only vaguely know about cursors. As I understand it, cursors are
    essentially temporary tables into which I can add values.
    Can you get me started on how to modify the code below to place the select
    results into a cursor and then use the cursor values to delete the
    corresponding procedures?

    Thanks Jeff


    select information_schema.ROUTINES.SPECIFIC_NAME
    from information_schema.routines
    where information_schema.ROUTINES.Routine_Schema = 'MyDB' and SPECIFIC_NAME
    like '%W1';

    Drop procedure 'MyDB' .XW1;




    --
    Posted via a free Usenet account from http://www.teranews.com

    Jeff Guest

  4. #4

    Default Re: Dropping Procedures - Accessign Procedure names

    On Sat, 14 Apr 2007 11:44:39 -0500, "Jeff" <com> wrote:
     

    You could have a function like this, returning the number of
    procedures deleted.

    CREATE FUNCTION `MyDB`.`delete_from_regexp`
    (_regexp VARCHAR(200))
    RETURNS INT
    BEGIN

    DECLARE _result INT;
    SET _result = 0;

    DECLARE _last_row TINYINT;
    DECLARE _procname VARCHAR(200);

    /* declare the cursor */
    DECLARE _allprocs CURSOR FOR
    select information_schema.ROUTINES.SPECIFIC_NAME
    from information_schema.routines
    where information_schema.ROUTINES.Routine_Schema = 'MyDB'
    and SPECIFIC_NAME like _regexp;

    /* to be able to test if there are other results in the cursor */
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET _last_row=1;

    SET _last_row = 0;
    OPEN _allprocs;
    FETCH _allprocs INTO _procname;
    IF _last_row = 0 THEN
    REPEAT
    DROP PRODCEDURE _procname ;
    SET _result=_result+1;
    FETCH _allprocs INTO _procname;
    UNTIL _last_row = 1
    END REPEAT;
    END IF;
    CLOSE _allprocs;

    RETURN(_result);
    END$$

    subtenante Guest

Similar Threads

  1. Case sensitive Stored Procedure names in IDS9.4
    By Colin Bull in forum Informix
    Replies: 0
    Last Post: September 23rd, 08:52 AM
  2. Dropping a recordset during a stored procedure
    By Mia in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 21st, 02:19 PM
  3. stored procedure that calls other procedures
    By Edgard in forum Microsoft SQL / MS SQL Server
    Replies: 4
    Last Post: July 19th, 04:30 PM
  4. how to list all the file names in a directory in Stored Procedure
    By David Portas in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: July 11th, 05:13 AM
  5. Replies: 1
    Last Post: July 10th, 01:39 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139