Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I've tried to show the SQL code of a stored procedure called add_pixel:

mysql> SHOW PROCEDURE CODE add_pixel;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

I've made sure that the procedure exists:

mysql> CALL add_pixel();
ERROR 1318 (42000): Incorrect number of arguments for PROCEDURE pixels.add_pixel; expected 3, got 0

And SHOW CREATE PROCEDURE works:

mysql> SHOW CREATE PROCEDURE add_pixel;
+-----------+----------+------------------+----------------------+----------------------+--------------------+
| Procedure | sql_mode | Create Procedure | character_set_client | collation_connection | Database Collation |
+-----------+----------+------------------+----------------------+----------------------+--------------------+
| add_pixel |          | NULL             | utf8                 | utf8_general_ci      | utf8_unicode_ci    |
+-----------+----------+------------------+----------------------+----------------------+--------------------+
1 row in set (0.00 sec)

and that my MySQL version supports `SHOW PROCEDURE (SHOW PROCEDURE CODE was added in MySQL 5.0.17).

How do I view the source SQL code of the procedure?

share|improve this question
    
Try SHOW CREATE PROCEDURE –  ypercube Aug 21 '12 at 9:36
1  
As your link explains about the SHOW PROCEDURE CODE: "This statement is a MySQL extension that is available only for servers that have been built with debugging support." –  ypercube Aug 21 '12 at 9:37
    
@ypercube SHOW CREATE works. I think you might be right about the debugging support. Care to post it as an answer? –  Adam Matan Aug 21 '12 at 9:40
add comment

1 Answer

up vote 6 down vote accepted

EDIT

Amazingly enough, INFORMATION_SCHEMA.ROUTINES does not have a param-list column.

You can use mysql.proc. For example, looking at a procedure of mine:

SELECT * FROM mysql.proc WHERE name='run' \G
*************************** 1. row ***************************
                  db: common_schema
                name: run
                type: PROCEDURE
       specific_name: run
            language: SQL
     sql_data_access: MODIFIES_SQL_DATA
    is_deterministic: YES
       security_type: INVOKER
          param_list:
  in query_script text

             returns:
                body: main_body: begin
  if (LEFT(query_script, 1) in ('/', '\\')) and (LEFT(query_script, 2) != '/*') then
    begin
          declare query_script_file_name text;

      set query_script_file_name := query_script;
      set query_script := LOAD_FILE(query_script_file_name);
      if query_script is null then
        call throw(CONCAT('Cannot load script file: ', query_script_file_name));
      end if;
    end;
  end if;
  call _interpret(query_script, TRUE);
end
             definer: root@localhost
             created: 2012-08-17 17:24:46
            modified: 2012-08-17 17:24:46
            sql_mode:
             comment: Run given QueryScript text
character_set_client: utf8
collation_connection: utf8_general_ci
        db_collation: latin1_swedish_ci
           body_utf8: main_body: begin
  if (LEFT(query_script, 1) in ('/', '\')) and (LEFT(query_script, 2) != '/*') then
    begin
          declare query_script_file_name text;

      set query_script_file_name := query_script;
      set query_script := LOAD_FILE(query_script_file_name);
      if query_script is null then
        call throw(CONCAT('Cannot load script file: ', query_script_file_name));
      end if;
    end;
  end if;
  call _interpret(query_script, TRUE);
end

Look for the param_list column (I believe that was your main concern). Also look for db, name, type.

=================

Alternatively, can use INFORMATION_SCHEMA.ROUTINES. For example, looking at a procedure of mine:

SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME='run' \G
*************************** 1. row ***************************
       SPECIFIC_NAME: run
     ROUTINE_CATALOG: NULL
      ROUTINE_SCHEMA: common_schema
        ROUTINE_NAME: run
        ROUTINE_TYPE: PROCEDURE
      DTD_IDENTIFIER: NULL
        ROUTINE_BODY: SQL
  ROUTINE_DEFINITION: main_body: begin
  if (LEFT(query_script, 1) in ('/', '\')) then
    begin
          declare query_script_file_name text;

      set query_script_file_name := query_script;
      set query_script := LOAD_FILE(query_script_file_name);
      if query_script is null then
        call throw(CONCAT('Cannot load script file: ', query_script_file_name));
      end if;
    end;
  end if;
  call _interpret(query_script, TRUE);
end
       EXTERNAL_NAME: NULL
   EXTERNAL_LANGUAGE: NULL
     PARAMETER_STYLE: SQL
    IS_DETERMINISTIC: YES
     SQL_DATA_ACCESS: MODIFIES SQL DATA
            SQL_PATH: NULL
       SECURITY_TYPE: INVOKER
             CREATED: 2012-08-17 17:24:46
        LAST_ALTERED: 2012-08-17 17:24:46
            SQL_MODE: 
     ROUTINE_COMMENT: Run given QueryScript text
             DEFINER: root@localhost
CHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION: utf8_general_ci
  DATABASE_COLLATION: latin1_swedish_ci

In particular, look at the columns ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE, ROUTINE_DEFINITION. These provide most of the interesting data.

share|improve this answer
    
I should note that the above only applies to MySQL 5.1. As of MySQL 5.5 there's the INFORMATION_SCHEMA.PARAMETERS table which provides routine params. –  Shlomi Noach Oct 17 '13 at 17:16
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.