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 |
def |
|
ROUTINE_SCHEMA |
db |
|
ROUTINE_NAME |
name |
|
ROUTINE_TYPE |
type |
{PROCEDURE|FUNCTION} |
DATA_TYPE |
same as for COLUMNS table |
|
CHARACTER_MAXIMUM_LENGTH |
same as for COLUMNS table |
|
CHARACTER_OCTET_LENGTH |
same as for COLUMNS table |
|
NUMERIC_PRECISION |
same as for COLUMNS table |
|
NUMERIC_SCALE |
same as for COLUMNS table |
|
CHARACTER_SET_NAME |
same as for COLUMNS table |
|
COLLATION_NAME |
same as for COLUMNS table |
|
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.
The DATA_TYPE
,
CHARACTER_MAXIMUM_LENGTH
,
CHARACTER_OCTET_LENGTH
,
NUMERIC_PRECISION
,
NUMERIC_SCALE
,
CHARACTER_SET_NAME
, and
COLLATION_NAME
columns provide information
about the data type for the RETURNS
clause
of stored functions. If a stored routine is a stored
procedure, these columns all are NULL
.
These columns were added in MySQL 5.5.3.
Information about stored function RETURNS
data types is also available in the
PARAMETERS
table. The return
value data type row for a function can be identified as the
row that has an ORDINAL_POSITION
value of
0.
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.