Edit File: information_schema_routines.result
# ========== routines.1 ========== USE INFORMATION_SCHEMA; SHOW CREATE TABLE INFORMATION_SCHEMA.ROUTINES; Table Create Table ROUTINES CREATE TEMPORARY TABLE `ROUTINES` ( `SPECIFIC_NAME` varchar(64) NOT NULL DEFAULT '', `ROUTINE_CATALOG` varchar(512) NOT NULL DEFAULT '', `ROUTINE_SCHEMA` varchar(64) NOT NULL DEFAULT '', `ROUTINE_NAME` varchar(64) NOT NULL DEFAULT '', `ROUTINE_TYPE` varchar(9) NOT NULL DEFAULT '', `DATA_TYPE` varchar(64) NOT NULL DEFAULT '', `CHARACTER_MAXIMUM_LENGTH` int(21) DEFAULT NULL, `CHARACTER_OCTET_LENGTH` int(21) DEFAULT NULL, `NUMERIC_PRECISION` int(21) DEFAULT NULL, `NUMERIC_SCALE` int(21) DEFAULT NULL, `CHARACTER_SET_NAME` varchar(64) DEFAULT NULL, `COLLATION_NAME` varchar(64) DEFAULT NULL, `DTD_IDENTIFIER` longtext, `ROUTINE_BODY` varchar(8) NOT NULL DEFAULT '', `ROUTINE_DEFINITION` longtext, `EXTERNAL_NAME` varchar(64) DEFAULT NULL, `EXTERNAL_LANGUAGE` varchar(64) DEFAULT NULL, `PARAMETER_STYLE` varchar(8) NOT NULL DEFAULT '', `IS_DETERMINISTIC` varchar(3) NOT NULL DEFAULT '', `SQL_DATA_ACCESS` varchar(64) NOT NULL DEFAULT '', `SQL_PATH` varchar(64) DEFAULT NULL, `SECURITY_TYPE` varchar(7) NOT NULL DEFAULT '', `CREATED` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `LAST_ALTERED` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `SQL_MODE` varchar(8192) NOT NULL DEFAULT '', `ROUTINE_COMMENT` longtext NOT NULL, `DEFINER` varchar(77) NOT NULL DEFAULT '', `CHARACTER_SET_CLIENT` varchar(32) NOT NULL DEFAULT '', `COLLATION_CONNECTION` varchar(32) NOT NULL DEFAULT '', `DATABASE_COLLATION` varchar(32) NOT NULL DEFAULT '' ) DEFAULT CHARSET=utf8 SELECT * FROM information_schema.columns WHERE table_schema = 'information_schema' AND table_name = 'routines' ORDER BY ordinal_position; TABLE_CATALOG def TABLE_SCHEMA information_schema TABLE_NAME ROUTINES COLUMN_NAME SPECIFIC_NAME ORDINAL_POSITION 1 COLUMN_DEFAULT IS_NULLABLE NO DATA_TYPE varchar CHARACTER_MAXIMUM_LENGTH 64 CHARACTER_OCTET_LENGTH 192 NUMERIC_PRECISION NULL NUMERIC_SCALE NULL CHARACTER_SET_NAME utf8 COLLATION_NAME utf8_general_ci COLUMN_TYPE varchar(64) COLUMN_KEY EXTRA PRIVILEGES # COLUMN_COMMENT TABLE_CATALOG def TABLE_SCHEMA information_schema TABLE_NAME ROUTINES COLUMN_NAME ROUTINE_CATALOG ORDINAL_POSITION 2 COLUMN_DEFAULT IS_NULLABLE NO DATA_TYPE varchar CHARACTER_MAXIMUM_LENGTH 512 CHARACTER_OCTET_LENGTH 1536 NUMERIC_PRECISION NULL NUMERIC_SCALE NULL CHARACTER_SET_NAME utf8 COLLATION_NAME utf8_general_ci COLUMN_TYPE varchar(512) COLUMN_KEY EXTRA PRIVILEGES # COLUMN_COMMENT TABLE_CATALOG def TABLE_SCHEMA information_schema TABLE_NAME ROUTINES COLUMN_NAME ROUTINE_SCHEMA ORDINAL_POSITION 3 COLUMN_DEFAULT IS_NULLABLE NO DATA_TYPE varchar CHARACTER_MAXIMUM_LENGTH 64 CHARACTER_OCTET_LENGTH 192 NUMERIC_PRECISION NULL NUMERIC_SCALE NULL CHARACTER_SET_NAME utf8 COLLATION_NAME utf8_general_ci COLUMN_TYPE varchar(64) COLUMN_KEY EXTRA PRIVILEGES # COLUMN_COMMENT TABLE_CATALOG def TABLE_SCHEMA information_schema TABLE_NAME ROUTINES COLUMN_NAME ROUTINE_NAME ORDINAL_POSITION 4 COLUMN_DEFAULT IS_NULLABLE NO DATA_TYPE varchar CHARACTER_MAXIMUM_LENGTH 64 CHARACTER_OCTET_LENGTH 192 NUMERIC_PRECISION NULL NUMERIC_SCALE NULL CHARACTER_SET_NAME utf8 COLLATION_NAME utf8_general_ci COLUMN_TYPE varchar(64) COLUMN_KEY EXTRA PRIVILEGES # COLUMN_COMMENT TABLE_CATALOG def TABLE_SCHEMA information_schema TABLE_NAME ROUTINES COLUMN_NAME ROUTINE_TYPE ORDINAL_POSITION 5 COLUMN_DEFAULT IS_NULLABLE NO DATA_TYPE varchar CHARACTER_MAXIMUM_LENGTH 9 CHARACTER_OCTET_LENGTH 27 NUMERIC_PRECISION NULL NUMERIC_SCALE NULL CHARACTER_SET_NAME utf8 COLLATION_NAME utf8_general_ci COLUMN_TYPE varchar(9) COLUMN_KEY EXTRA PRIVILEGES # COLUMN_COMMENT TABLE_CATALOG def TABLE_SCHEMA information_schema TABLE_NAME ROUTINES COLUMN_NAME DATA_TYPE ORDINAL_POSITION 6 COLUMN_DEFAULT IS_NULLABLE NO DATA_TYPE varchar CHARACTER_MAXIMUM_LENGTH 64 CHARACTER_OCTET_LENGTH 192 NUMERIC_PRECISION NULL NUMERIC_SCALE NULL CHARACTER_SET_NAME utf8 COLLATION_NAME utf8_general_ci COLUMN_TYPE varchar(64) COLUMN_KEY EXTRA PRIVILEGES # COLUMN_COMMENT TABLE_CATALOG def TABLE_SCHEMA information_schema TABLE_NAME ROUTINES COLUMN_NAME CHARACTER_MAXIMUM_LENGTH ORDINAL_POSITION 7 COLUMN_DEFAULT NULL IS_NULLABLE YES DATA_TYPE int CHARACTER_MAXIMUM_LENGTH NULL CHARACTER_OCTET_LENGTH NULL NUMERIC_PRECISION 10 NUMERIC_SCALE 0 CHARACTER_SET_NAME NULL COLLATION_NAME NULL COLUMN_TYPE int(21) COLUMN_KEY EXTRA PRIVILEGES # COLUMN_COMMENT TABLE_CATALOG def TABLE_SCHEMA information_schema TABLE_NAME ROUTINES COLUMN_NAME CHARACTER_OCTET_LENGTH ORDINAL_POSITION 8 COLUMN_DEFAULT NULL IS_NULLABLE YES DATA_TYPE int CHARACTER_MAXIMUM_LENGTH NULL CHARACTER_OCTET_LENGTH NULL NUMERIC_PRECISION 10 NUMERIC_SCALE 0 CHARACTER_SET_NAME NULL COLLATION_NAME NULL COLUMN_TYPE int(21) COLUMN_KEY EXTRA PRIVILEGES # COLUMN_COMMENT TABLE_CATALOG def TABLE_SCHEMA information_schema TABLE_NAME ROUTINES COLUMN_NAME NUMERIC_PRECISION ORDINAL_POSITION 9 COLUMN_DEFAULT NULL IS_NULLABLE YES DATA_TYPE int CHARACTER_MAXIMUM_LENGTH NULL CHARACTER_OCTET_LENGTH NULL NUMERIC_PRECISION 10 NUMERIC_SCALE 0 CHARACTER_SET_NAME NULL COLLATION_NAME NULL COLUMN_TYPE int(21) COLUMN_KEY EXTRA PRIVILEGES # COLUMN_COMMENT TABLE_CATALOG def TABLE_SCHEMA information_schema TABLE_NAME ROUTINES COLUMN_NAME NUMERIC_SCALE ORDINAL_POSITION 10 COLUMN_DEFAULT NULL IS_NULLABLE YES DATA_TYPE int CHARACTER_MAXIMUM_LENGTH NULL CHARACTER_OCTET_LENGTH NULL NUMERIC_PRECISION 10 NUMERIC_SCALE 0 CHARACTER_SET_NAME NULL COLLATION_NAME NULL COLUMN_TYPE int(21) COLUMN_KEY EXTRA PRIVILEGES # COLUMN_COMMENT TABLE_CATALOG def TABLE_SCHEMA information_schema TABLE_NAME ROUTINES COLUMN_NAME CHARACTER_SET_NAME ORDINAL_POSITION 11 COLUMN_DEFAULT NULL IS_NULLABLE YES DATA_TYPE varchar CHARACTER_MAXIMUM_LENGTH 64 CHARACTER_OCTET_LENGTH 192 NUMERIC_PRECISION NULL NUMERIC_SCALE NULL CHARACTER_SET_NAME utf8 COLLATION_NAME utf8_general_ci COLUMN_TYPE varchar(64) COLUMN_KEY EXTRA PRIVILEGES # COLUMN_COMMENT TABLE_CATALOG def TABLE_SCHEMA information_schema TABLE_NAME ROUTINES COLUMN_NAME COLLATION_NAME ORDINAL_POSITION 12 COLUMN_DEFAULT NULL IS_NULLABLE YES DATA_TYPE varchar CHARACTER_MAXIMUM_LENGTH 64 CHARACTER_OCTET_LENGTH 192 NUMERIC_PRECISION NULL NUMERIC_SCALE NULL CHARACTER_SET_NAME utf8 COLLATION_NAME utf8_general_ci COLUMN_TYPE varchar(64) COLUMN_KEY EXTRA PRIVILEGES # COLUMN_COMMENT TABLE_CATALOG def TABLE_SCHEMA information_schema TABLE_NAME ROUTINES COLUMN_NAME DTD_IDENTIFIER ORDINAL_POSITION 13 COLUMN_DEFAULT NULL IS_NULLABLE YES DATA_TYPE longtext CHARACTER_MAXIMUM_LENGTH 4294967295 CHARACTER_OCTET_LENGTH 4294967295 NUMERIC_PRECISION NULL NUMERIC_SCALE NULL CHARACTER_SET_NAME utf8 COLLATION_NAME utf8_general_ci COLUMN_TYPE longtext COLUMN_KEY EXTRA PRIVILEGES # COLUMN_COMMENT TABLE_CATALOG def TABLE_SCHEMA information_schema TABLE_NAME ROUTINES COLUMN_NAME ROUTINE_BODY ORDINAL_POSITION 14 COLUMN_DEFAULT IS_NULLABLE NO DATA_TYPE varchar CHARACTER_MAXIMUM_LENGTH 8 CHARACTER_OCTET_LENGTH 24 NUMERIC_PRECISION NULL NUMERIC_SCALE NULL CHARACTER_SET_NAME utf8 COLLATION_NAME utf8_general_ci COLUMN_TYPE varchar(8) COLUMN_KEY EXTRA PRIVILEGES # COLUMN_COMMENT TABLE_CATALOG def TABLE_SCHEMA information_schema TABLE_NAME ROUTINES COLUMN_NAME ROUTINE_DEFINITION ORDINAL_POSITION 15 COLUMN_DEFAULT NULL IS_NULLABLE YES DATA_TYPE longtext CHARACTER_MAXIMUM_LENGTH 4294967295 CHARACTER_OCTET_LENGTH 4294967295 NUMERIC_PRECISION NULL NUMERIC_SCALE NULL CHARACTER_SET_NAME utf8 COLLATION_NAME utf8_general_ci COLUMN_TYPE longtext COLUMN_KEY EXTRA PRIVILEGES # COLUMN_COMMENT TABLE_CATALOG def TABLE_SCHEMA information_schema TABLE_NAME ROUTINES COLUMN_NAME EXTERNAL_NAME ORDINAL_POSITION 16 COLUMN_DEFAULT NULL IS_NULLABLE YES DATA_TYPE varchar CHARACTER_MAXIMUM_LENGTH 64 CHARACTER_OCTET_LENGTH 192 NUMERIC_PRECISION NULL NUMERIC_SCALE NULL CHARACTER_SET_NAME utf8 COLLATION_NAME utf8_general_ci COLUMN_TYPE varchar(64) COLUMN_KEY EXTRA PRIVILEGES # COLUMN_COMMENT TABLE_CATALOG def TABLE_SCHEMA information_schema TABLE_NAME ROUTINES COLUMN_NAME EXTERNAL_LANGUAGE ORDINAL_POSITION 17 COLUMN_DEFAULT NULL IS_NULLABLE YES DATA_TYPE varchar CHARACTER_MAXIMUM_LENGTH 64 CHARACTER_OCTET_LENGTH 192 NUMERIC_PRECISION NULL NUMERIC_SCALE NULL CHARACTER_SET_NAME utf8 COLLATION_NAME utf8_general_ci COLUMN_TYPE varchar(64) COLUMN_KEY EXTRA PRIVILEGES # COLUMN_COMMENT TABLE_CATALOG def TABLE_SCHEMA information_schema TABLE_NAME ROUTINES COLUMN_NAME PARAMETER_STYLE ORDINAL_POSITION 18 COLUMN_DEFAULT IS_NULLABLE NO DATA_TYPE varchar CHARACTER_MAXIMUM_LENGTH 8 CHARACTER_OCTET_LENGTH 24 NUMERIC_PRECISION NULL NUMERIC_SCALE NULL CHARACTER_SET_NAME utf8 COLLATION_NAME utf8_general_ci COLUMN_TYPE varchar(8) COLUMN_KEY EXTRA PRIVILEGES # COLUMN_COMMENT TABLE_CATALOG def TABLE_SCHEMA information_schema TABLE_NAME ROUTINES COLUMN_NAME IS_DETERMINISTIC ORDINAL_POSITION 19 COLUMN_DEFAULT IS_NULLABLE NO DATA_TYPE varchar CHARACTER_MAXIMUM_LENGTH 3 CHARACTER_OCTET_LENGTH 9 NUMERIC_PRECISION NULL NUMERIC_SCALE NULL CHARACTER_SET_NAME utf8 COLLATION_NAME utf8_general_ci COLUMN_TYPE varchar(3) COLUMN_KEY EXTRA PRIVILEGES # COLUMN_COMMENT TABLE_CATALOG def TABLE_SCHEMA information_schema TABLE_NAME ROUTINES COLUMN_NAME SQL_DATA_ACCESS ORDINAL_POSITION 20 COLUMN_DEFAULT IS_NULLABLE NO DATA_TYPE varchar CHARACTER_MAXIMUM_LENGTH 64 CHARACTER_OCTET_LENGTH 192 NUMERIC_PRECISION NULL NUMERIC_SCALE NULL CHARACTER_SET_NAME utf8 COLLATION_NAME utf8_general_ci COLUMN_TYPE varchar(64) COLUMN_KEY EXTRA PRIVILEGES # COLUMN_COMMENT TABLE_CATALOG def TABLE_SCHEMA information_schema TABLE_NAME ROUTINES COLUMN_NAME SQL_PATH ORDINAL_POSITION 21 COLUMN_DEFAULT NULL IS_NULLABLE YES DATA_TYPE varchar CHARACTER_MAXIMUM_LENGTH 64 CHARACTER_OCTET_LENGTH 192 NUMERIC_PRECISION NULL NUMERIC_SCALE NULL CHARACTER_SET_NAME utf8 COLLATION_NAME utf8_general_ci COLUMN_TYPE varchar(64) COLUMN_KEY EXTRA PRIVILEGES # COLUMN_COMMENT TABLE_CATALOG def TABLE_SCHEMA information_schema TABLE_NAME ROUTINES COLUMN_NAME SECURITY_TYPE ORDINAL_POSITION 22 COLUMN_DEFAULT IS_NULLABLE NO DATA_TYPE varchar CHARACTER_MAXIMUM_LENGTH 7 CHARACTER_OCTET_LENGTH 21 NUMERIC_PRECISION NULL NUMERIC_SCALE NULL CHARACTER_SET_NAME utf8 COLLATION_NAME utf8_general_ci COLUMN_TYPE varchar(7) COLUMN_KEY EXTRA PRIVILEGES # COLUMN_COMMENT TABLE_CATALOG def TABLE_SCHEMA information_schema TABLE_NAME ROUTINES COLUMN_NAME CREATED ORDINAL_POSITION 23 COLUMN_DEFAULT 0000-00-00 00:00:00 IS_NULLABLE NO DATA_TYPE datetime CHARACTER_MAXIMUM_LENGTH NULL CHARACTER_OCTET_LENGTH NULL NUMERIC_PRECISION NULL NUMERIC_SCALE NULL CHARACTER_SET_NAME NULL COLLATION_NAME NULL COLUMN_TYPE datetime COLUMN_KEY EXTRA PRIVILEGES # COLUMN_COMMENT TABLE_CATALOG def TABLE_SCHEMA information_schema TABLE_NAME ROUTINES COLUMN_NAME LAST_ALTERED ORDINAL_POSITION 24 COLUMN_DEFAULT 0000-00-00 00:00:00 IS_NULLABLE NO DATA_TYPE datetime CHARACTER_MAXIMUM_LENGTH NULL CHARACTER_OCTET_LENGTH NULL NUMERIC_PRECISION NULL NUMERIC_SCALE NULL CHARACTER_SET_NAME NULL COLLATION_NAME NULL COLUMN_TYPE datetime COLUMN_KEY EXTRA PRIVILEGES # COLUMN_COMMENT TABLE_CATALOG def TABLE_SCHEMA information_schema TABLE_NAME ROUTINES COLUMN_NAME SQL_MODE ORDINAL_POSITION 25 COLUMN_DEFAULT IS_NULLABLE NO DATA_TYPE varchar CHARACTER_MAXIMUM_LENGTH 8192 CHARACTER_OCTET_LENGTH 24576 NUMERIC_PRECISION NULL NUMERIC_SCALE NULL CHARACTER_SET_NAME utf8 COLLATION_NAME utf8_general_ci COLUMN_TYPE varchar(8192) COLUMN_KEY EXTRA PRIVILEGES # COLUMN_COMMENT TABLE_CATALOG def TABLE_SCHEMA information_schema TABLE_NAME ROUTINES COLUMN_NAME ROUTINE_COMMENT ORDINAL_POSITION 26 COLUMN_DEFAULT NULL IS_NULLABLE NO DATA_TYPE longtext CHARACTER_MAXIMUM_LENGTH 4294967295 CHARACTER_OCTET_LENGTH 4294967295 NUMERIC_PRECISION NULL NUMERIC_SCALE NULL CHARACTER_SET_NAME utf8 COLLATION_NAME utf8_general_ci COLUMN_TYPE longtext COLUMN_KEY EXTRA PRIVILEGES # COLUMN_COMMENT TABLE_CATALOG def TABLE_SCHEMA information_schema TABLE_NAME ROUTINES COLUMN_NAME DEFINER ORDINAL_POSITION 27 COLUMN_DEFAULT IS_NULLABLE NO DATA_TYPE varchar CHARACTER_MAXIMUM_LENGTH 77 CHARACTER_OCTET_LENGTH 231 NUMERIC_PRECISION NULL NUMERIC_SCALE NULL CHARACTER_SET_NAME utf8 COLLATION_NAME utf8_general_ci COLUMN_TYPE varchar(77) COLUMN_KEY EXTRA PRIVILEGES # COLUMN_COMMENT TABLE_CATALOG def TABLE_SCHEMA information_schema TABLE_NAME ROUTINES COLUMN_NAME CHARACTER_SET_CLIENT ORDINAL_POSITION 28 COLUMN_DEFAULT IS_NULLABLE NO DATA_TYPE varchar CHARACTER_MAXIMUM_LENGTH 32 CHARACTER_OCTET_LENGTH 96 NUMERIC_PRECISION NULL NUMERIC_SCALE NULL CHARACTER_SET_NAME utf8 COLLATION_NAME utf8_general_ci COLUMN_TYPE varchar(32) COLUMN_KEY EXTRA PRIVILEGES # COLUMN_COMMENT TABLE_CATALOG def TABLE_SCHEMA information_schema TABLE_NAME ROUTINES COLUMN_NAME COLLATION_CONNECTION ORDINAL_POSITION 29 COLUMN_DEFAULT IS_NULLABLE NO DATA_TYPE varchar CHARACTER_MAXIMUM_LENGTH 32 CHARACTER_OCTET_LENGTH 96 NUMERIC_PRECISION NULL NUMERIC_SCALE NULL CHARACTER_SET_NAME utf8 COLLATION_NAME utf8_general_ci COLUMN_TYPE varchar(32) COLUMN_KEY EXTRA PRIVILEGES # COLUMN_COMMENT TABLE_CATALOG def TABLE_SCHEMA information_schema TABLE_NAME ROUTINES COLUMN_NAME DATABASE_COLLATION ORDINAL_POSITION 30 COLUMN_DEFAULT IS_NULLABLE NO DATA_TYPE varchar CHARACTER_MAXIMUM_LENGTH 32 CHARACTER_OCTET_LENGTH 96 NUMERIC_PRECISION NULL NUMERIC_SCALE NULL CHARACTER_SET_NAME utf8 COLLATION_NAME utf8_general_ci COLUMN_TYPE varchar(32) COLUMN_KEY EXTRA PRIVILEGES # COLUMN_COMMENT DESCRIBE INFORMATION_SCHEMA.ROUTINES; Field Type Null Key Default Extra SPECIFIC_NAME varchar(64) NO ROUTINE_CATALOG varchar(512) NO ROUTINE_SCHEMA varchar(64) NO ROUTINE_NAME varchar(64) NO ROUTINE_TYPE varchar(9) NO DATA_TYPE varchar(64) NO CHARACTER_MAXIMUM_LENGTH int(21) YES NULL CHARACTER_OCTET_LENGTH int(21) YES NULL NUMERIC_PRECISION int(21) YES NULL NUMERIC_SCALE int(21) YES NULL CHARACTER_SET_NAME varchar(64) YES NULL COLLATION_NAME varchar(64) YES NULL DTD_IDENTIFIER longtext YES NULL ROUTINE_BODY varchar(8) NO ROUTINE_DEFINITION longtext YES NULL EXTERNAL_NAME varchar(64) YES NULL EXTERNAL_LANGUAGE varchar(64) YES NULL PARAMETER_STYLE varchar(8) NO IS_DETERMINISTIC varchar(3) NO SQL_DATA_ACCESS varchar(64) NO SQL_PATH varchar(64) YES NULL SECURITY_TYPE varchar(7) NO CREATED datetime NO 0000-00-00 00:00:00 LAST_ALTERED datetime NO 0000-00-00 00:00:00 SQL_MODE varchar(8192) NO ROUTINE_COMMENT longtext NO NULL DEFINER varchar(77) NO CHARACTER_SET_CLIENT varchar(32) NO COLLATION_CONNECTION varchar(32) NO DATABASE_COLLATION varchar(32) NO # ========== routines.2 ========== DROP DATABASE IF EXISTS i_s_routines_test; CREATE DATABASE i_s_routines_test; USE i_s_routines_test; CREATE FUNCTION test_func1 (s char(20) RETURNS CHAR(50) RETURN CONCAT('Hello', ,s,'!'); ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'RETURNS CHAR(50) RETURN CONCAT('Hello', ,s,'!')' at line 1 SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'i_s_routines_test' AND ROUTINE_NAME = 'test_func1'; SPECIFIC_NAME ROUTINE_CATALOG ROUTINE_SCHEMA ROUTINE_NAME ROUTINE_TYPE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE CHARACTER_SET_NAME COLLATION_NAME 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 CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION # ========== routines.3 ========== DROP DATABASE IF EXISTS i_s_routines_test; CREATE DATABASE i_s_routines_test; USE i_s_routines_test; CREATE FUNCTION test_func1 (s char(20)) RETURNS CHAR(50) RETURN CONCAT('Hello, ',s,'!'); SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'i_s_routines_test' AND ROUTINE_NAME = 'test_func1'; SPECIFIC_NAME ROUTINE_CATALOG ROUTINE_SCHEMA ROUTINE_NAME ROUTINE_TYPE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE CHARACTER_SET_NAME COLLATION_NAME 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 CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION test_func1 def i_s_routines_test test_func1 FUNCTION char 50 50 NULL NULL latin1 latin1_swedish_ci char(50) SQL RETURN CONCAT('Hello, ',s,'!') NULL NULL SQL NO CONTAINS SQL NULL DEFINER <created> <modified> root@localhost latin1 latin1_swedish_ci latin1_swedish_ci DROP FUNCTION test_func1; SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'i_s_routines_test' AND ROUTINE_NAME = 'test_func1'; SPECIFIC_NAME ROUTINE_CATALOG ROUTINE_SCHEMA ROUTINE_NAME ROUTINE_TYPE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE CHARACTER_SET_NAME COLLATION_NAME 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 CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION # ========== routines.4 ========== DROP DATABASE IF EXISTS i_s_routines_test; CREATE DATABASE i_s_routines_test; USE i_s_routines_test; CREATE PROCEDURE testproc (OUT param1 INT) BEGIN SELECT 2+2 as param1; END; // SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'i_s_routines_test' AND ROUTINE_NAME = 'testproc'; SPECIFIC_NAME ROUTINE_CATALOG ROUTINE_SCHEMA ROUTINE_NAME ROUTINE_TYPE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE CHARACTER_SET_NAME COLLATION_NAME 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 CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION testproc def i_s_routines_test testproc PROCEDURE NULL NULL NULL NULL NULL NULL NULL SQL BEGIN SELECT 2+2 as param1; END NULL NULL SQL NO CONTAINS SQL NULL DEFINER <created> <modified> root@localhost latin1 latin1_swedish_ci latin1_swedish_ci # ========== routines.5 ========== DROP DATABASE IF EXISTS i_s_routines_test; CREATE DATABASE i_s_routines_test; USE i_s_routines_test; CREATE FUNCTION test_func1 (s char(20)) RETURNS CHAR(50) RETURN CONCAT('Hello, ',s,'!'); SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'i_s_routines_test' AND ROUTINE_NAME = 'test_func1'; SPECIFIC_NAME ROUTINE_CATALOG ROUTINE_SCHEMA ROUTINE_NAME ROUTINE_TYPE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE CHARACTER_SET_NAME COLLATION_NAME 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 CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION test_func1 def i_s_routines_test test_func1 FUNCTION char 50 50 NULL NULL latin1 latin1_swedish_ci char(50) SQL RETURN CONCAT('Hello, ',s,'!') NULL NULL SQL NO CONTAINS SQL NULL DEFINER <created> <modified> root@localhost latin1 latin1_swedish_ci latin1_swedish_ci # ========== routines.6 ========== DROP DATABASE IF EXISTS i_s_routines_test; CREATE DATABASE i_s_routines_test; USE i_s_routines_test; CREATE FUNCTION test_func2 (s int) RETURNS INT RETURN s*2; SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'i_s_routines_test' AND ROUTINE_NAME = 'test_func2'; SPECIFIC_NAME ROUTINE_CATALOG ROUTINE_SCHEMA ROUTINE_NAME ROUTINE_TYPE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE CHARACTER_SET_NAME COLLATION_NAME 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 CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION test_func2 def i_s_routines_test test_func2 FUNCTION int NULL NULL 10 0 NULL NULL int(11) SQL RETURN s*2 NULL NULL SQL NO CONTAINS SQL NULL DEFINER <created> <modified> root@localhost latin1 latin1_swedish_ci latin1_swedish_ci # ========== routines.7 ========== DROP DATABASE IF EXISTS i_s_routines_test; CREATE DATABASE i_s_routines_test; USE i_s_routines_test; CREATE FUNCTION test_func5 (s date) RETURNS TIMESTAMP RETURN CURRENT_TIMESTAMP; SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'i_s_routines_test' AND ROUTINE_NAME = 'test_func5'; SPECIFIC_NAME ROUTINE_CATALOG ROUTINE_SCHEMA ROUTINE_NAME ROUTINE_TYPE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE CHARACTER_SET_NAME COLLATION_NAME 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 CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION test_func5 def i_s_routines_test test_func5 FUNCTION timestamp NULL NULL NULL NULL NULL NULL timestamp SQL RETURN CURRENT_TIMESTAMP NULL NULL SQL NO CONTAINS SQL NULL DEFINER <created> <modified> root@localhost latin1 latin1_swedish_ci latin1_swedish_ci # ========== routines.8 ========== DROP DATABASE IF EXISTS i_s_routines_test; CREATE DATABASE i_s_routines_test; USE i_s_routines_test; CREATE FUNCTION test_func5 (s date) RETURNS TIMESTAMP RETURN CURRENT_TIMESTAMP; SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'i_s_routines_test' AND ROUTINE_NAME = 'test_func5'; SPECIFIC_NAME ROUTINE_CATALOG ROUTINE_SCHEMA ROUTINE_NAME ROUTINE_TYPE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE CHARACTER_SET_NAME COLLATION_NAME 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 CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION test_func5 def i_s_routines_test test_func5 FUNCTION timestamp NULL NULL NULL NULL NULL NULL timestamp SQL RETURN CURRENT_TIMESTAMP NULL NULL SQL NO CONTAINS SQL NULL DEFINER <created> <modified> root@localhost latin1 latin1_swedish_ci latin1_swedish_ci ALTER FUNCTION test_func5 COMMENT 'new comment added'; SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'i_s_routines_test' AND ROUTINE_NAME = 'test_func5'; SPECIFIC_NAME ROUTINE_CATALOG ROUTINE_SCHEMA ROUTINE_NAME ROUTINE_TYPE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE CHARACTER_SET_NAME COLLATION_NAME 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 CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION test_func5 def i_s_routines_test test_func5 FUNCTION timestamp NULL NULL NULL NULL NULL NULL timestamp SQL RETURN CURRENT_TIMESTAMP NULL NULL SQL NO CONTAINS SQL NULL DEFINER <created> <modified> new comment added root@localhost latin1 latin1_swedish_ci latin1_swedish_ci # ========== routines.9 ========== DROP DATABASE IF EXISTS i_s_routines_test; CREATE DATABASE i_s_routines_test CHARACTER SET utf8; USE i_s_routines_test; CREATE FUNCTION test_func5 (s CHAR(20)) RETURNS VARCHAR(30) RETURN CONCAT('XYZ, ' ,s); SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'i_s_routines_test' AND ROUTINE_NAME = 'test_func5'; SPECIFIC_NAME ROUTINE_CATALOG ROUTINE_SCHEMA ROUTINE_NAME ROUTINE_TYPE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE CHARACTER_SET_NAME COLLATION_NAME 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 CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION test_func5 def i_s_routines_test test_func5 FUNCTION varchar 30 90 NULL NULL utf8 utf8_general_ci varchar(30) SQL RETURN CONCAT('XYZ, ' ,s) NULL NULL SQL NO CONTAINS SQL NULL DEFINER <created> <modified> root@localhost latin1 latin1_swedish_ci utf8_general_ci DROP DATABASE i_s_routines_test;
Back to File Manager