Create a procedure to list the tables with detailed information in a particular database.

MySQLMySQLi 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)
raja
Updated on 22-Jun-2020 06:51:19

Advertisements