Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Create a procedure to list the tables with detailed information in a particular database.
Suppose currently we are using a database named ‘query’ and it is having the following tables in it −
mysql> Show tables in query; +-----------------+ | Tables_in_query | +-----------------+ | student_detail | | student_info | +-----------------+ 2 rows in set (0.00 sec)
Now, following is a stored procedure, which will give us the list of tables with detailed information −
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)
Updated on: 22-Jun-2020
42 Views
Advertisements