3

How to get the total count of stored procedures in a MySQL database? Do I look in the information schema?

rlandster
  • 7,294
  • 14
  • 58
  • 96
Danish Kp
  • 49
  • 1
  • 11
  • 1
    Yes you should check `INFORMATION_SCHEMA`. Specifically check [this answer](http://stackoverflow.com/a/36700359/643104) – Mahesh Nov 23 '16 at 07:55
  • Possible duplicate of [List of Stored Procedures/Functions Mysql Command Line](http://stackoverflow.com/questions/733349/list-of-stored-procedures-functions-mysql-command-line) – Mahesh Nov 23 '16 at 07:56
  • Total? For all users, or just the stored procedures you have access to? – jarlh Nov 23 '16 at 08:34

3 Answers3

10

You can use this to see the list of Procedures

SELECT ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE="PROCEDURE" 
AND ROUTINE_SCHEMA="dbname";

You can find out more here

Sharvil Popli
  • 158
  • 2
  • 9
3

Try this solution, just replace your database name in the last line.

SELECT count(ROUTINE_NAME)procedure_count
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE="PROCEDURE" 
AND ROUTINE_SCHEMA="your database name";
Vishal
  • 1,232
  • 1
  • 6
  • 8
  • An explanation of your code is always needed, as others should be able to learn from your code. Additionally, please don't be rude – Nico Haase Feb 25 '19 at 13:42
0

This worked for me

SELECT     
    COUNT(*) as 'Stored Procedures'     
FROM SYS.OBJECTS
WHERE TYPE ='P'
GROUP BY TYPE
pradeepradyumna
  • 952
  • 13
  • 34