현재 'query'라는 데이터베이스를 사용 중이고 그 안에 다음 테이블이 있다고 가정합니다. −
mysql> Show tables in query; +-----------------+ | Tables_in_query | +-----------------+ | student_detail | | student_info | +-----------------+ 2 rows in set (0.00 sec)
다음은 자세한 정보가 포함된 테이블 목록을 제공하는 저장 프로시저입니다. −
mysql> DELIMITER// mysql> CREATE procedure tablelist() -> BEGIN -> Select * from Information_schema.tables WHERE table_schema = 'query'; -> END // Query OK, 0 rows affected (0.06 sec) mysql> DELIMITER; mysql> CALL tablelist()\G *************************** 1. row *************************** TABLE_CATALOG: def TABLE_SCHEMA: query TABLE_NAME: student_detail TABLE_TYPE: BASE TABLE ENGINE: InnoDB VERSION: 10 ROW_FORMAT: Dynamic TABLE_ROWS: 4 AVG_ROW_LENGTH: 4096 DATA_LENGTH: 16384 MAX_DATA_LENGTH: 0 INDEX_LENGTH: 0 DATA_FREE: 0 AUTO_INCREMENT: NULL CREATE_TIME: 2017-12-13 16:25:44 UPDATE_TIME: NULL CHECK_TIME: NULL TABLE_COLLATION: latin1_swedish_ci CHECKSUM: NULL CREATE_OPTIONS: TABLE_COMMENT: *************************** 2. row *************************** TABLE_CATALOG: def TABLE_SCHEMA: query TABLE_NAME: student_info TABLE_TYPE: BASE TABLE ENGINE: InnoDB VERSION: 10 ROW_FORMAT: Dynamic TABLE_ROWS: 4 AVG_ROW_LENGTH: 4096 DATA_LENGTH: 16384 MAX_DATA_LENGTH: 0 INDEX_LENGTH: 0 DATA_FREE: 0 AUTO_INCREMENT: NULL CREATE_TIME: 2017-12-12 09:52:51 UPDATE_TIME: NULL CHECK_TIME: NULL TABLE_COLLATION: latin1_swedish_ci CHECKSUM: NULL CREATE_OPTIONS: TABLE_COMMENT: 2 rows in set (0.01 sec) Query OK, 0 rows affected (0.06 sec)