57

I created a stored procedure in phpMyAdmin

CREATE PROCEDURE Sample()
SELECT * FROM feedback

Where could I view this this procedure? If it's not possible in phpMyAdmin, what is/are some good program(s) that have the feature to write, store, and view stored procedures, tables, etc?

user229044
  • 232,980
  • 40
  • 330
  • 338
Dennis Martinez
  • 6,344
  • 11
  • 50
  • 67
  • According to the [phpMyAdmin home page](http://www.phpmyadmin.net/home_page/index.php), it has the ability to "manage stored procedures and triggers," but I can't for the life of me figure out how. – rockerest May 24 '11 at 19:16
  • 14
    For the love of everything that is holy on the web and for your own sanity - please, please do not use that crap PHPMyAdmin, there are so many GUI tools out there that are better and aren't posing a huge, huge security risk plus they don't have terrible usability. One of those is MySQL Workbench (http://www.mysql.com/downloads/workbench/) and the other one is SQLYog(http://www.webyog.com). – N.B. Aug 18 '11 at 11:20
  • Damn, I'm glad I found this thread and the comment about MySQL Workbench! Thanks N.B.! I'm just starting with PHP/MySQL and didn't even know that more advanced tools existed. Workbench reminds me MS SQL Management Studio a lot and this is exactly what I need! And it was so easy to get it to work - just unpack the zip, run, click connect and it's all set up! – Val Oct 02 '12 at 13:23
  • 3
    @N.B: Yeah, but many web host services neither give you shell access, nor expose their MySQL database server on the internet, so often your only option is using a preinstalled PHPMyAdmin. – Calmarius Feb 05 '13 at 23:06
  • @N.B I would like to play devils advocate and ask you to please cite a source that specifically addresses your opinion pertaining to the "huge, huge security risk" in PHPMyAdmin. Specifically, all of the proposed steps to secure a server for PHPMyAdmin use are applicable to the use of any PHP script (presumably already running) and even more so to the use of any remote DB management software, including MySQL Workbench and SQLYog. Source: http://stackoverflow.com/questions/3661911/does-phpmyadmin-pose-security-risk-on-production As for usability, that's just a preference and not worth debating. – Strixy May 31 '13 at 17:38
  • @Strixy - [here](http://www.phpmyadmin.net/home_page/security/) a nice history of security holes that keep getting "fixed". What you said is notoriously wrong, remote management db software does not impose the same security risks as PhpMyAdmin nor is it even capable of such a "feature". It's a piece of software that has a known history of problems, it's code is far from nice and readable - thus patching it isn't exactly quick, and there's plethora of outdated PhpMyAdmin installations out there. To keep it short - it's really a piece of shit of a software. I wouldn't recommend to use it, ever. – N.B. May 31 '13 at 22:57
  • @N.B. I did not say that remote management db software imposes the same security risks. I'm not even sure how you could interpret what I said as such. My position is that neither approach is secure until you take measures to secure them. Using strongly worded opinions and mis-representing my position is a little offensive, but most importantly suggesting that MySQL Workbench is secure is dangerous. When challenged to support your claim you were unable to provide more than a change log. MySQL Workbench and Server both provide public change logs and both include security related fixes. – Strixy Jun 06 '13 at 19:24
  • Without phpMyAdmin, how will you manage your database from your smartphone? :)) – hakan Sep 20 '13 at 08:54

11 Answers11

76

View stored procedures in phpmyadmin:

Query:

SELECT routine_definition
FROM information_schema.routines
WHERE 
routine_name = 'procedure_name' AND routine_schema = 'databasename';

Here's how to get there in phpmyadmin.

phpmyadmin screenshot

The routines option is available in phpmyadmin. The link is not visible in PHPmyadmin until you have at least one stored procedure. See the above image and click the routines link under structure tab.

ZygD
  • 22,092
  • 39
  • 79
  • 102
ravi404
  • 7,119
  • 4
  • 31
  • 40
  • Not all PHPMyAdmin installations have it. The one on my server at 1&1, just don't have this link (PHPMyAdmin on my localhost have it), and fails in many way with stored procedure. The OP would better go with Adminer, I posted about in this topic. Anyway, you answer is still worth a vote. – Hibou57 Sep 30 '12 at 10:36
  • 1
    @Hibou57 Try to change the theme to `original`, i could find a new tab "Routines" next to "Privileges" – HamZa Jan 21 '13 at 00:32
  • 1
    I think it's important to say(because it happened to me) that the "Routines" link won't show unless you actually have stored procedures defined for the DB. So if this is the first time you will use them, just run the query in the query box, and then go back to the database and look for the Routines link, there you will see the stored procedure you just created. – gadget00 Feb 06 '13 at 22:40
  • +1 for the screen shot. Wow, you seem to be the only one that makes screen shot of visual tool, and not pasting a script. – Bakudan Oct 19 '13 at 17:52
  • 1
    In case you can't see the state from the image: The routines link shows under the tables list database structure tab. So click on the database name to see the tables in the database and then click on the structure tab and after the list of tables the routines link shows, but only if you have routines already. – sdjuan Nov 13 '13 at 23:13
  • Bizarrely for me the link is there and it shows a bunch of routines (as expected), however, clicking edit brings up an empty textarea... although the stored procedures must be there because they are callable! – geoidesic Sep 11 '14 at 14:02
  • On my version - you **click on your database** and there it is, **Routines tab** on the middle/right. – ZygD Feb 11 '17 at 15:47
21
select routine_definition
from information_schema.routines
where routine_schema = 'db_name'
and routine_name = 'sp_name';
Nicola Cossu
  • 54,599
  • 15
  • 92
  • 98
6

This answer shows how to view them without a script.

"Once you created the stored procedure it will appear in the Routines fieldset below your tables (in the Structure tab), and you can easily change/drop it."

Community
  • 1
  • 1
Felipe Barnett
  • 407
  • 4
  • 10
5

under phpMyAdmin, click on your database (Not on the table), then click on "+Routines".

There you can edit/drop all your stored procedures

Karim
  • 51
  • 1
  • 1
5

In PHPMYADMIN enter image description here3.5.2.2 version you just click on routines link in top. See the attached image

Mahadeva Prasad
  • 709
  • 8
  • 19
5

In short you can use this sql

SHOW CREATE PROCEDURE Sample;

More information here

UPDATE: If you don't remember the names, you can query the INFORMATION_SCHEMA database to see all the procedures (well you can use a LIKE on ROUTINE_NAME, if you remember a partial name)

SELECT ROUTINE_TYPE, ROUTINE_NAME, ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA='dbname';
Aravindan R
  • 3,084
  • 1
  • 28
  • 44
5

You can select "information_schema" as database and query all entries form the table "routines", in case u don't want to use SQL everytime.

David Snabel-Caunt
  • 57,804
  • 13
  • 114
  • 132
steffen
  • 51
  • 1
  • 1
  • This is the best answer.. to see. – Rafee Feb 21 '12 at 05:43
  • To select information_schema select another database and in SQL tab query `USE information_schema` (mysteriously "USE db_name" with only server root selected brings to the main page not to db_name) – Salvador Jun 30 '12 at 13:01
4
show procedure status;      -- will show you the stored procedures.
show create procedure MY_PROC;  -- will show you the definition of a procedure. 
help show;          -- show all the available options for the show command.
scumdogg
  • 452
  • 1
  • 6
  • 15
2

Don't forget that in smaller screens you'll have to use the "more" menu. phpMyAdmin

Rodrigo Polo
  • 4,314
  • 2
  • 26
  • 32
1

After clicking home, databases, the database I created the procedure in. It opens the structure window of that database. In the menu bar: "structure, sql, search ,..." there should be routines, if it's not then click on the right item called more and it should be there (curse my netbook for not having a 24 inch screen).

To make sure your database has the procedure click on export, choose "Custom - display all possible options", under "Output:" choose "View output as text", under "Format-specific options:" choose "structure" (just under "dump table"),make sure "Add CREATE PROCEDURE / FUNCTION / EVENT statement" is selected (just a little under "dump table"). Now click Go and your procedure should show up

using: Version information: 3.5.2, latest stable version: 3.5.2.2

HMR
  • 37,593
  • 24
  • 91
  • 160
1

Use the Adminer data-base interface. Unlike PHPMyAdmin, it's perfectly able to view, edit and invoke stored procedures, where PHPMyAdmin fails with tons of errors (errors when you try to run an SQL statement to create one, errors when you try to invoke one, errors when you try to alter one already created, beside of its inability to list the ones defined… I really wonder what PHPMyAdmin do with with SQL queries text before it submit it to the DB, that's frightening).

Just copy the Adminer PHP file at some location of you web server, open the corresponding URL. After you logged-in and selected a data-base, below the list of tables, you will see a list of the stored procedures, with a Call button. Clicking on the procedure link, you will also be able to alter (edit) it.

Honestly, I recommand you gave up with PHPMyAdmin, it's perfectly incapable of properly dealing with this (note that SQLBuddy too, fails in some way with that).

-- edit --

For completeness, you may also list stored procedures with this SQL query:

show procedure status;

Or this one, to retrieve a procedure whose name is known:

show procedure status where Name = 'name';
Hibou57
  • 6,870
  • 6
  • 52
  • 56