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