Metadata
With release of version 5.0.2 MySQL now has addition methods for retrieving information about stored routines. You can use SHOW as you have been able to with tables but also the new information schema tables
Contents |
[edit] SHOW
If you have previous experience of using MySQL you may already have used SHOW to display information about the MySQL setup or tables within the database. You can use SHOW to get information about functions and procedures you have created.
The first way to use it is to show the create statement, this is good if you have lost the source file you used to create the program or if you created it directly in MySQL. The syntax to use show create is as follows.
HOW CREATE {function / procedure} sp_name
We can try this on one of the functions we have created.
show create function helloworld // +------------+----------+------------------------------------------------------------------------------ | Function | sql_mode | Create Function +------------+----------+------------------------------------------------------------------------------ | helloworld | | CREATE FUNCTION `pers`.`helloworld`(param1 varchar(100),param2 varchar(100)) | | | RETURNS varchar(100) | | | begin | | | | | | return CONCAT(param1,´ ´,param2) ; | | | | | | end +------------+----------+------------------------------------------------------------------------------ 1 row in set (0.00 sec)
And the same for a procedure.
show create procedure helloprocedure // +----------------+----------+--------------------------------------------------------------------------- | Procedure | sql_mode | Create Procedure +----------------+----------+--------------------------------------------------------------------------- | helloprocedure | | CREATE PROCEDURE `pers`.`helloprocedure` | | | (param1 VARCHAR(100),INOUT param2 VARCHAR(100)) | | | begin | | | | | | set param2 = concat(param1,´ ´,param2); | | | | | | end +----------------+----------+--------------------------------------------------------------------------- 1 row in set (0.02 sec)
[edit] SHOW STATUS
The second use of SHOW with procedures and functions is SHOW STATUS. This displays various characteristics of the program such as its name, type and creation and modification dates.The syntax is as follows.
SHOW {function / procedure} STATUS [LIKE ´pattern´]
However when we tried to get this working the server kept crashing. This is another reminder that were working with very new technologies here. Hopefully this will be fixed in a future release, the MySQL documentation does show us what we should expect to see.
Update I tried this show syntax on Mac OS X and it performed as documented, the crash we reported was on the Windows XP version of MySQL. So if your using anything other than XP please try the syntax and let us know how you get on.
SHOW FUNCTION STATUS LIKE ´hello´G
*************************** 1. row ***************************
Db: test
Name: hello
Type: FUNCTION
Definer: testuser@localhost
Modified: 2004-08-03 15:29:37
Created: 2004-08-03 15:29:37
Security_type: DEFINER
Comment:
[edit] INFORMATION SCHEMA
The information schema is available in version 5.0.2 and up of MySQL. The schema allows us to view Metadata about objects within MySQL such as Tables, columns, privileges and of more relevance to us store routines. Metadata is commonly referred to data about data, if you can query tables within MySQL then you will be able to use the Information Schema to give you information on your stored routines.
We will limit ourselves to talk about stored routines but you may want to look at the MySQL documentation for details on what´s available for other database objects.
There are a number of columns we can select from the information schema, these are as follows.
SPECIFIC_NAME ROUTINE_CATALOG ROUTINE_SCHEMA ROUTINE_NAME ROUTINE_TYPE DTD_IDENTIFIER ROUTINE_BODY ROUTINE_DEFINITION EXTERNAL_NAME EXTERNAL_LANGUAGE PARAMETER_STYLE IS_DETERMINISTIC SQL_DATA_ACCESS SQL_PATH SECURITY_TYPE CREATED LAST_ALTERED SQL_MODE ROUTINE_COMMENT DEFINER
You can use any combination of these columns to extract information about a specific routine, a group of routines or all routines within the database using standard SQL statements.
For example if we wanted to see a list of all the routines in the system we could use the following SQL.
select routine_name from information_schema.routines // +--------------+ | routine_name | +--------------+ | calcsalary | | calcsalary | | getage | | isemployee | | ismember | +--------------+ 5 rows in set (0.01 sec)
This lists all of the routines in the system, however we can see two routines with the same name. It might be better if we can see the database the routines belongs too and also which type of routine it is as there is no distinction in this list between procedures and functions.
select routine_name, routine_schema, routine_type from information_schema.routines// +--------------+----------------+--------------+ | routine_name | routine_schema | routine_type | +--------------+----------------+--------------+ | calcsalary | payroll | PROCEDURE | | calcsalary | pers | PROCEDURE | | getage | pers | PROCEDURE | | isemployee | pers | FUNCTION | | ismember | pers | PROCEDURE | +--------------+----------------+--------------+ 5 rows in set (0.00 sec)
This time we can see the distinct between the different routines. We might want to see only the routines for the database we have currently selected. To do this we can use a where statement and include a call to the database() function like so.
select routine_name, routine_schema, routine_type from information_schema.routines where routine_schema = database() // +--------------+----------------+--------------+ | routine_name | routine_schema | routine_type | +--------------+----------------+--------------+ | calcsalary | pers | PROCEDURE | | getage | pers | PROCEDURE | | isemployee | pers | FUNCTION | | ismember | pers | PROCEDURE | +--------------+----------------+--------------+ 4 rows in set (0.01 sec)
This time we can see just the routines for the currently selected database.
We can include as many columns as needed and use standard SQL where clause syntax to select only the specific rows we need. We will leave it up to you to discover what the information_schema.routines table can offer you as further discussion will likely become repetitive but this short introduction does give an indication of the usefulness.
[edit] MYSQL.PROC
The final way to retrieve METADATA for stored procedures is to use the mysql.proc table. This table functions in the same way as the information_schema but contains slightly less information. The column names for mysql.proc are also different to those used in the information schema.
db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment
Data is selected from mysql.proc in the same way as information_schema and has the same flexibility with regards to standard SQL syntax.
select name, db, type from mysql.proc // +------------+---------+-----------+ | name | db | type | +------------+---------+-----------+ | calcsalary | payroll | PROCEDURE | | calcsalary | pers | PROCEDURE | | getage | pers | PROCEDURE | | isemployee | pers | FUNCTION | | ismember | pers | PROCEDURE | +------------+---------+-----------+ 5 rows in set (0.02 sec)
Which method to use is at your discretion, information_schema is ANSI standard and you may have used it with other databases which support it such as SQL Server.
So far we have looked at the basics of how to construct stored procedures within MySQL, next we will be looking at some more complex concepts within the language which would ideally be placed at the end of our tutorials but they are important to understand for a very useful part of database programming.
It may be worth reviewing what we have learnt so far before going on to the next section. Perhaps using the knowledge you have gained so far you could create some of your own functions and procedures.