The ROUTINES
table provides
information about stored routines (both procedures and functions).
The ROUTINES
table does not include
user-defined functions (UDFs) at this time.
The column named “mysql.proc
name”
indicates the mysql.proc
table column that
corresponds to the
INFORMATION_SCHEMA.ROUTINES
table
column, if any.
INFORMATION_SCHEMA
Name |
mysql.proc Name |
Remarks |
SPECIFIC_NAME |
specific_name |
|
ROUTINE_CATALOG |
NULL |
|
ROUTINE_SCHEMA |
db |
|
ROUTINE_NAME |
name |
|
ROUTINE_TYPE |
type |
{PROCEDURE|FUNCTION} |
DTD_IDENTIFIER |
data type descriptor | |
ROUTINE_BODY |
SQL |
|
ROUTINE_DEFINITION |
body |
|
EXTERNAL_NAME |
NULL |
|
EXTERNAL_LANGUAGE |
language |
NULL |
PARAMETER_STYLE |
SQL |
|
IS_DETERMINISTIC |
is_deterministic |
|
SQL_DATA_ACCESS |
sql_data_access |
|
SQL_PATH |
NULL |
|
SECURITY_TYPE |
security_type |
|
CREATED |
created |
|
LAST_ALTERED |
modified |
|
SQL_MODE |
sql_mode |
MySQL extension |
ROUTINE_COMMENT |
comment |
MySQL extension |
DEFINER |
definer |
MySQL extension |
CHARACTER_SET_CLIENT |
MySQL extension | |
COLLATION_CONNECTION |
MySQL extension | |
DATABASE_COLLATION |
MySQL extension |
Notes:
MySQL calculates EXTERNAL_LANGUAGE
thus:
If mysql.proc.language='SQL'
,
EXTERNAL_LANGUAGE
is
NULL
Otherwise, EXTERNAL_LANGUAGE
is what is
in mysql.proc.language
. However, we do
not have external languages yet, so it is always
NULL
.
CHARACTER_SET_CLIENT
is the session value
of the character_set_client
system variable when the routine was created.
COLLATION_CONNECTION
is the session value
of the collation_connection
system variable when the routine was created.
DATABASE_COLLATION
is the collation of the
database with which the routine is associated. These columns
were added in MySQL 5.1.21.
User Comments
In case you would want to view all the stored procedures in a Database then we can use :
SELECT ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE="PROCEDURE"
AND ROUTINE_SCHEMA="dbname";
You could use the same statement for FUNCTIONS just set
ROUTINE_TYPE="FUNCTION" in the WHERE clause
Add your own comment.