Is there a way to view all stored procedures and stored functions in a given database?
Appendix A. Frequently Asked Questions About MySQL 5.1Yes. For a database named dbname, use this query on the INFORMATION_SCHEMA.ROUTINES table: SELECT ROUTINE_TYPE, ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA='dbname'; The body of a stored routine can be viewed using SHOW CREATE FUNCTION (for a stored function) or SHOW CREATE PROCEDURE (for a stored procedure). See Section 13.5.4.8, "SHOW CREATE PROCEDURE and SHOW CREATE FUNCTION Syntax", for more information.
Related QuestionsWhat is the difference between stored procedures and functions?
PL/SQL FAQ - Oracle FAQFunctions are normally used for computations where as procedures are normally used for executing business logic. You can have DML (insert,update, delete) statements in a function. But, you cannot call such a function in a SQL query. For example, if you have a function that is updating a table, you cannot call that function from a SQL query. - select myFunction(field) from sometable; will throw error. Stored Procedure: supports deferred name resolution.
Related QuestionsAre stored procedures and functions created on a master server replicated to a slave?
Appendix A. Frequently Asked Questions About MySQL 5.1Yes, creation of stored procedures and functions carried out through normal DDL statements on a master server are replicated to a slave, so the objects will exist on both servers. ALTER and DROP statements for stored procedures and functions are also replicated.
Related QuestionsDo MySQL 5.1 stored procedures and functions work with replication?
Appendix A. Frequently Asked Questions About MySQL 5.1Yes, standard actions carried out in stored procedures and functions are replicated from a master MySQL server to a slave server. There are a few limitations that are described in detail in Section 18.4, "Binary Logging of Stored Routines and Triggers".
Related QuestionsHow are actions that take place inside stored procedures and functions replicated?
Appendix A. Frequently Asked Questions About MySQL 5.1MySQL records each DML event that occurs in a stored procedure and replicates those individual actions to a slave server. The actual calls made to execute stored procedures are not replicated. Stored functions that change data are logged as function invocations, not as the DML events that occur inside each function. Yes. Because a slave server has authority to execute any statement read from a master's binary log, special security constraints exist for using stored functions with replication.
Related QuestionsWhere are stored procedures stored?
Appendix A. Frequently Asked Questions About MySQL 5.1In the proc table of the mysql system database. However, you should not access the tables in the system database directly. Instead, use SHOW CREATE FUNCTION to obtain information about stored functions, and SHOW CREATE PROCEDURE to obtain information about stored procedures. See Section 13.5.4.8, "SHOW CREATE PROCEDURE and SHOW CREATE FUNCTION Syntax", for more information about these statements. You can also query the ROUTINES table in the INFORMATION_SCHEMA database - see Section 22.
Related QuestionsWhat is the difference between stand alone procedures and stored procedures?
PL/SQL FAQ - Page 8Latest Answer: An Oracle stored procedure is a program stored in an Oracle databaseStandalone procedures are the pr...
Related QuestionsCan SQL Compare handle stored procedures and functions that were renamed using sp_rename?
SQL Compare - Frequently asked questionsYes. If a database schema has become inconsistent due to the use of sp_rename, and a stored procedure or a function definition refers to a different name from what is in the system tables, SQL Compare 6 automatically fixes these inconsistencies.
Related QuestionsWhere can I find the ANSI SQL 2003 specification for stored procedures?
Appendix A. Frequently Asked Questions About MySQL 5.1Unfortunately, the official specifications are not freely available (ANSI makes them available for purchase). However, there are books - such as SQL-99 Complete, Really by Peter Gulutzan and Trudy Pelzer - which give a comprehensive overview of the standard, including coverage of stored procedures.
Related QuestionsDo stored procedures have a statement for raising application errors?
Appendix A. Frequently Asked Questions About MySQL 5.1Not in MySQL 5.1. We intend to implement the SQL standard SIGNAL and RESIGNAL statements in a future MySQL release.
Related QuestionsDo stored procedures provide exception handling?
Appendix A. Frequently Asked Questions About MySQL 5.1MySQL implements HANDLER definitions according to the SQL standard. See Section 18.2.8.2, "DECLARE Handlers", for details.
Related QuestionsWhat's the difference between needle aponeurotomy and other procedures?
NA-FAQI've already had surgery for Dupuytren's. Can I still have a needle procedure? Yes - if you have developed cords beneath fairly normal skin. However, needle aponeurotomy may not be possible after surgery if you have tight or hard skin, tight scars, or if no remaining cord can be felt. In these situations, traditional surgery may be the best solution, possibly with a skin graft.
Related Questionsnetworking faqs: networking faqsA stored procedure i s named collection of SQL statements and procedural logic that is compiled, verified and stored in a server database. It is typically treated like any other database object. Stored procedures accept input parameters so that a single procedure can be used over the network by multiple clients using different input data. A single remote message triggers the execution of a collection of stored SQL statements. The results is a reduction of network traffic and better performance.Related Questions
MySQL AB :: MySQL 5.1 Reference Manual :: A.4 MySQL 5.1 FAQ ...In the proc table of the mysql system database. However, you should not access the tables in the system database directly. Instead, use SHOW CREATE FUNCTION to obtain information about stored functions, and SHOW CREATE PROCEDURE to obtain information about stored procedures. See Section 11.5.4.8, "SHOW CREATE PROCEDURE and SHOW CREATE FUNCTION Syntax", for more information about these statements. You can also query the ROUTINES table in the INFORMATION_SCHEMA database - see Section 22.Related Questions
