Create a MySQL stored procedure that takes the database name as a parameter and lists the tables containing detailed information in a specific database.

Suppose currently we are using a database named "query" which contains the following table -

mysql> Show tables in query;
+-----------------+
| Tables_in_query |
+-----------------+
| student_detail  |
| student_info    |
+-----------------+
2 rows in set (0.00 sec)

Now, the following is a stored procedure that will Accepts the name of the database as its parameter and gives us the list of tables with the details -

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 call 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)

The above is the detailed content of Create a MySQL stored procedure that takes the database name as a parameter and lists the tables containing detailed information in a specific database.. For more information, please follow other related articles on the PHP Chinese website!