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 MySQL stored procedure, which takes the name of the database as its parameter, 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 accept the name of the database as its parameter and give us the list of tables with detailed information −
mysql> DELIMITER//
mysql> CREATE procedure tb_list(db_name varchar(40))
-> BEGIN
-> SET @z := CONCAT('Select * from information_schema.tables WHERE table_schema = ','\'',db_name,'\'');
-> Prepare stmt from @z;
-> EXECUTE stmt;
-> END //
Query OK, 0 rows affected (0.06 sec)Now invoke this stored procedure by providing the name of the database as its parameter −
mysql> DELIMITER;
mysql> CALL tb_list('query')\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.00 sec)
Updated on: 22-Jun-2020
273 Views
Advertisements