1DROP DATABASE IF EXISTS test1; 2DROP DATABASE IF EXISTS test2; 3CREATE DATABASE test1; 4CREATE DATABASE test2; 5CREATE TABLE test1.t1 (f1 VARCHAR(20)) ENGINE = <engine_to_be_used>; 6CREATE TABLE test1.t2 (f1 VARCHAR(20)) ENGINE = <engine_to_be_used>; 7CREATE TABLE test2.t1 (f1 VARCHAR(20)) ENGINE = <engine_to_be_used>; 8SELECT *, 9LEFT( table_comment, 10IF(INSTR(table_comment,'InnoDB free') = 0 11AND INSTR(table_comment,'number_of_replicas') = 0, 12LENGTH(table_comment), 13INSTR(table_comment,'InnoDB free') 14+ INSTR(table_comment,'number_of_replicas') - 1)) 15AS "user_comment", 16'-----------------------------------------------------' AS "Separator" 17FROM information_schema.tables 18WHERE table_schema LIKE 'test%' AND table_type = 'BASE TABLE' 19ORDER BY table_schema,table_name; 20TABLE_CATALOG def 21TABLE_SCHEMA test1 22TABLE_NAME t1 23TABLE_TYPE BASE TABLE 24ENGINE InnoDB 25VERSION 10 26ROW_FORMAT Compact 27TABLE_ROWS #TBLR# 28AVG_ROW_LENGTH #ARL# 29DATA_LENGTH #DL# 30MAX_DATA_LENGTH #MDL# 31INDEX_LENGTH #IL# 32DATA_FREE #DF# 33AUTO_INCREMENT NULL 34CREATE_TIME #CRT# 35UPDATE_TIME #UT# 36CHECK_TIME #CT# 37TABLE_COLLATION latin1_swedish_ci 38CHECKSUM NULL 39CREATE_OPTIONS #CO# 40TABLE_COMMENT #TC# 41user_comment 42Separator ----------------------------------------------------- 43TABLE_CATALOG def 44TABLE_SCHEMA test1 45TABLE_NAME t2 46TABLE_TYPE BASE TABLE 47ENGINE InnoDB 48VERSION 10 49ROW_FORMAT Compact 50TABLE_ROWS #TBLR# 51AVG_ROW_LENGTH #ARL# 52DATA_LENGTH #DL# 53MAX_DATA_LENGTH #MDL# 54INDEX_LENGTH #IL# 55DATA_FREE #DF# 56AUTO_INCREMENT NULL 57CREATE_TIME #CRT# 58UPDATE_TIME #UT# 59CHECK_TIME #CT# 60TABLE_COLLATION latin1_swedish_ci 61CHECKSUM NULL 62CREATE_OPTIONS #CO# 63TABLE_COMMENT #TC# 64user_comment 65Separator ----------------------------------------------------- 66TABLE_CATALOG def 67TABLE_SCHEMA test2 68TABLE_NAME t1 69TABLE_TYPE BASE TABLE 70ENGINE InnoDB 71VERSION 10 72ROW_FORMAT Compact 73TABLE_ROWS #TBLR# 74AVG_ROW_LENGTH #ARL# 75DATA_LENGTH #DL# 76MAX_DATA_LENGTH #MDL# 77INDEX_LENGTH #IL# 78DATA_FREE #DF# 79AUTO_INCREMENT NULL 80CREATE_TIME #CRT# 81UPDATE_TIME #UT# 82CHECK_TIME #CT# 83TABLE_COLLATION latin1_swedish_ci 84CHECKSUM NULL 85CREATE_OPTIONS #CO# 86TABLE_COMMENT #TC# 87user_comment 88Separator ----------------------------------------------------- 89SHOW TABLES FROM test1; 90Tables_in_test1 91t1 92t2 93SHOW TABLES FROM test2; 94Tables_in_test2 95t1 96DROP USER testuser1@localhost; 97CREATE USER testuser1@localhost; 98GRANT SELECT ON test1.* TO testuser1@localhost; 99# Establish connection testuser1 (user=testuser1) 100SELECT *, 101LEFT( table_comment, 102IF(INSTR(table_comment,'InnoDB free') = 0 103AND INSTR(table_comment,'number_of_replicas') = 0, 104LENGTH(table_comment), 105INSTR(table_comment,'InnoDB free') 106+ INSTR(table_comment,'number_of_replicas') - 1)) 107AS "user_comment", 108'-----------------------------------------------------' AS "Separator" 109FROM information_schema.tables 110WHERE table_schema LIKE 'test%' AND table_type = 'BASE TABLE' 111ORDER BY table_schema,table_name; 112TABLE_CATALOG def 113TABLE_SCHEMA test1 114TABLE_NAME t1 115TABLE_TYPE BASE TABLE 116ENGINE InnoDB 117VERSION 10 118ROW_FORMAT Compact 119TABLE_ROWS #TBLR# 120AVG_ROW_LENGTH #ARL# 121DATA_LENGTH #DL# 122MAX_DATA_LENGTH #MDL# 123INDEX_LENGTH #IL# 124DATA_FREE #DF# 125AUTO_INCREMENT NULL 126CREATE_TIME #CRT# 127UPDATE_TIME #UT# 128CHECK_TIME #CT# 129TABLE_COLLATION latin1_swedish_ci 130CHECKSUM NULL 131CREATE_OPTIONS #CO# 132TABLE_COMMENT #TC# 133user_comment 134Separator ----------------------------------------------------- 135TABLE_CATALOG def 136TABLE_SCHEMA test1 137TABLE_NAME t2 138TABLE_TYPE BASE TABLE 139ENGINE InnoDB 140VERSION 10 141ROW_FORMAT Compact 142TABLE_ROWS #TBLR# 143AVG_ROW_LENGTH #ARL# 144DATA_LENGTH #DL# 145MAX_DATA_LENGTH #MDL# 146INDEX_LENGTH #IL# 147DATA_FREE #DF# 148AUTO_INCREMENT NULL 149CREATE_TIME #CRT# 150UPDATE_TIME #UT# 151CHECK_TIME #CT# 152TABLE_COLLATION latin1_swedish_ci 153CHECKSUM NULL 154CREATE_OPTIONS #CO# 155TABLE_COMMENT #TC# 156user_comment 157Separator ----------------------------------------------------- 158SHOW TABLES FROM test1; 159Tables_in_test1 160t1 161t2 162SHOW TABLES FROM test2; 163ERROR 42000: Access denied for user 'testuser1'@'localhost' to database 'test2' 164# Switch to connection default and close connection testuser1 165DROP USER testuser1@localhost; 166DROP DATABASE test1; 167DROP DATABASE test2; 168