summaryrefslogtreecommitdiff
path: root/mysql-test/r/information_schema_all_engines.result
blob: 126a6f4bccd412f629b375176f7308de12227d2e (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
use INFORMATION_SCHEMA;
show tables;
Tables_in_information_schema
ALL_PLUGINS
APPLICABLE_ROLES
CHARACTER_SETS
CLIENT_STATISTICS
COLLATIONS
COLLATION_CHARACTER_SET_APPLICABILITY
COLUMNS
COLUMN_PRIVILEGES
ENABLED_ROLES
ENGINES
EVENTS
FILES
GEOMETRY_COLUMNS
GLOBAL_STATUS
GLOBAL_VARIABLES
INDEX_STATISTICS
INNODB_BUFFER_PAGE
INNODB_BUFFER_PAGE_LRU
INNODB_BUFFER_POOL_STATS
INNODB_CMP
INNODB_CMPMEM
INNODB_CMPMEM_RESET
INNODB_CMP_PER_INDEX
INNODB_CMP_RESET
INNODB_LOCKS
INNODB_LOCK_WAITS
INNODB_METRICS
INNODB_MUTEXES
INNODB_SYS_COLUMNS
INNODB_SYS_FIELDS
INNODB_SYS_FOREIGN
INNODB_SYS_FOREIGN_COLS
INNODB_SYS_INDEXES
INNODB_SYS_TABLES
INNODB_SYS_TABLESTATS
INNODB_SYS_VIRTUAL
INNODB_TABLESPACES_ENCRYPTION
INNODB_TABLESPACES_SCRUBBING
INNODB_TRX
KEY_CACHES
KEY_COLUMN_USAGE
PARAMETERS
PARTITIONS
PLUGINS
PROCESSLIST
PROFILING
REFERENTIAL_CONSTRAINTS
ROUTINES
SCHEMATA
SCHEMA_PRIVILEGES
SESSION_STATUS
SESSION_VARIABLES
SPATIAL_REF_SYS
STATISTICS
SYSTEM_VARIABLES
TABLES
TABLESPACES
TABLE_CONSTRAINTS
TABLE_PRIVILEGES
TABLE_STATISTICS
TRIGGERS
USER_PRIVILEGES
USER_STATISTICS
VIEWS
SELECT t.table_name, c1.column_name
FROM information_schema.tables t
INNER JOIN
information_schema.columns c1
ON t.table_schema = c1.table_schema AND
t.table_name = c1.table_name
WHERE t.table_schema = 'information_schema' AND
c1.ordinal_position =
( SELECT COALESCE(MIN(c2.ordinal_position),1)
FROM information_schema.columns c2
WHERE c2.table_schema = t.table_schema AND
c2.table_name = t.table_name AND
c2.column_name LIKE '%SCHEMA%'
        ) order by t.table_name;
table_name	column_name
ALL_PLUGINS	PLUGIN_NAME
APPLICABLE_ROLES	GRANTEE
CHARACTER_SETS	CHARACTER_SET_NAME
CLIENT_STATISTICS	CLIENT
COLLATIONS	COLLATION_NAME
COLLATION_CHARACTER_SET_APPLICABILITY	COLLATION_NAME
COLUMNS	TABLE_SCHEMA
COLUMN_PRIVILEGES	TABLE_SCHEMA
ENABLED_ROLES	ROLE_NAME
ENGINES	ENGINE
EVENTS	EVENT_SCHEMA
FILES	TABLE_SCHEMA
GEOMETRY_COLUMNS	F_TABLE_SCHEMA
GLOBAL_STATUS	VARIABLE_NAME
GLOBAL_VARIABLES	VARIABLE_NAME
INDEX_STATISTICS	TABLE_SCHEMA
INNODB_BUFFER_PAGE	POOL_ID
INNODB_BUFFER_PAGE_LRU	POOL_ID
INNODB_BUFFER_POOL_STATS	POOL_ID
INNODB_CMP	page_size
INNODB_CMPMEM	page_size
INNODB_CMPMEM_RESET	page_size
INNODB_CMP_PER_INDEX	database_name
INNODB_CMP_RESET	page_size
INNODB_LOCKS	lock_id
INNODB_LOCK_WAITS	requesting_trx_id
INNODB_METRICS	NAME
INNODB_MUTEXES	NAME
INNODB_SYS_COLUMNS	TABLE_ID
INNODB_SYS_FIELDS	INDEX_ID
INNODB_SYS_FOREIGN	ID
INNODB_SYS_FOREIGN_COLS	ID
INNODB_SYS_INDEXES	INDEX_ID
INNODB_SYS_TABLES	TABLE_ID
INNODB_SYS_TABLESTATS	TABLE_ID
INNODB_SYS_VIRTUAL	TABLE_ID
INNODB_TABLESPACES_ENCRYPTION	SPACE
INNODB_TABLESPACES_SCRUBBING	SPACE
INNODB_TRX	trx_id
KEY_CACHES	KEY_CACHE_NAME
KEY_COLUMN_USAGE	CONSTRAINT_SCHEMA
PARAMETERS	SPECIFIC_SCHEMA
PARTITIONS	TABLE_SCHEMA
PLUGINS	PLUGIN_NAME
PROCESSLIST	ID
PROFILING	QUERY_ID
REFERENTIAL_CONSTRAINTS	CONSTRAINT_SCHEMA
ROUTINES	ROUTINE_SCHEMA
SCHEMATA	SCHEMA_NAME
SCHEMA_PRIVILEGES	TABLE_SCHEMA
SESSION_STATUS	VARIABLE_NAME
SESSION_VARIABLES	VARIABLE_NAME
SPATIAL_REF_SYS	SRID
STATISTICS	TABLE_SCHEMA
SYSTEM_VARIABLES	VARIABLE_NAME
TABLES	TABLE_SCHEMA
TABLESPACES	TABLESPACE_NAME
TABLE_CONSTRAINTS	CONSTRAINT_SCHEMA
TABLE_PRIVILEGES	TABLE_SCHEMA
TABLE_STATISTICS	TABLE_SCHEMA
TRIGGERS	TRIGGER_SCHEMA
USER_PRIVILEGES	GRANTEE
USER_STATISTICS	USER
VIEWS	TABLE_SCHEMA
SELECT t.table_name, c1.column_name
FROM information_schema.tables t
INNER JOIN
information_schema.columns c1
ON t.table_schema = c1.table_schema AND
t.table_name = c1.table_name
WHERE t.table_schema = 'information_schema' AND
c1.ordinal_position =
( SELECT COALESCE(MIN(c2.ordinal_position),1)
FROM information_schema.columns c2
WHERE c2.table_schema = 'information_schema' AND
c2.table_name = t.table_name AND
c2.column_name LIKE '%SCHEMA%'
        ) order by t.table_name;
table_name	column_name
ALL_PLUGINS	PLUGIN_NAME
APPLICABLE_ROLES	GRANTEE
CHARACTER_SETS	CHARACTER_SET_NAME
CLIENT_STATISTICS	CLIENT
COLLATIONS	COLLATION_NAME
COLLATION_CHARACTER_SET_APPLICABILITY	COLLATION_NAME
COLUMNS	TABLE_SCHEMA
COLUMN_PRIVILEGES	TABLE_SCHEMA
ENABLED_ROLES	ROLE_NAME
ENGINES	ENGINE
EVENTS	EVENT_SCHEMA
FILES	TABLE_SCHEMA
GEOMETRY_COLUMNS	F_TABLE_SCHEMA
GLOBAL_STATUS	VARIABLE_NAME
GLOBAL_VARIABLES	VARIABLE_NAME
INDEX_STATISTICS	TABLE_SCHEMA
INNODB_BUFFER_PAGE	POOL_ID
INNODB_BUFFER_PAGE_LRU	POOL_ID
INNODB_BUFFER_POOL_STATS	POOL_ID
INNODB_CMP	page_size
INNODB_CMPMEM	page_size
INNODB_CMPMEM_RESET	page_size
INNODB_CMP_PER_INDEX	database_name
INNODB_CMP_RESET	page_size
INNODB_LOCKS	lock_id
INNODB_LOCK_WAITS	requesting_trx_id
INNODB_METRICS	NAME
INNODB_MUTEXES	NAME
INNODB_SYS_COLUMNS	TABLE_ID
INNODB_SYS_FIELDS	INDEX_ID
INNODB_SYS_FOREIGN	ID
INNODB_SYS_FOREIGN_COLS	ID
INNODB_SYS_INDEXES	INDEX_ID
INNODB_SYS_TABLES	TABLE_ID
INNODB_SYS_TABLESTATS	TABLE_ID
INNODB_SYS_VIRTUAL	TABLE_ID
INNODB_TABLESPACES_ENCRYPTION	SPACE
INNODB_TABLESPACES_SCRUBBING	SPACE
INNODB_TRX	trx_id
KEY_CACHES	KEY_CACHE_NAME
KEY_COLUMN_USAGE	CONSTRAINT_SCHEMA
PARAMETERS	SPECIFIC_SCHEMA
PARTITIONS	TABLE_SCHEMA
PLUGINS	PLUGIN_NAME
PROCESSLIST	ID
PROFILING	QUERY_ID
REFERENTIAL_CONSTRAINTS	CONSTRAINT_SCHEMA
ROUTINES	ROUTINE_SCHEMA
SCHEMATA	SCHEMA_NAME
SCHEMA_PRIVILEGES	TABLE_SCHEMA
SESSION_STATUS	VARIABLE_NAME
SESSION_VARIABLES	VARIABLE_NAME
SPATIAL_REF_SYS	SRID
STATISTICS	TABLE_SCHEMA
SYSTEM_VARIABLES	VARIABLE_NAME
TABLES	TABLE_SCHEMA
TABLESPACES	TABLESPACE_NAME
TABLE_CONSTRAINTS	CONSTRAINT_SCHEMA
TABLE_PRIVILEGES	TABLE_SCHEMA
TABLE_STATISTICS	TABLE_SCHEMA
TRIGGERS	TRIGGER_SCHEMA
USER_PRIVILEGES	GRANTEE
USER_STATISTICS	USER
VIEWS	TABLE_SCHEMA
select 1 as "must be 1" from information_schema.tables  where "ACCOUNTS"=
(select cast(table_name as char)  from information_schema.tables
order by table_name limit 1) limit 1;
must be 1
1
select t.table_name, group_concat(t.table_schema, '.', t.table_name),
count(*) as num1
from information_schema.tables t
inner join information_schema.columns c1
on t.table_schema = c1.table_schema AND t.table_name = c1.table_name
where t.table_schema = 'information_schema' and
c1.ordinal_position =
(select isnull(c2.column_type) -
isnull(group_concat(c2.table_schema, '.', c2.table_name)) +
count(*) as num
from information_schema.columns c2 where
c2.table_schema='information_schema' and
(c2.column_type = 'varchar(7)' or c2.column_type = 'varchar(20)')
group by c2.column_type order by num limit 1)
group by t.table_name order by num1, t.table_name;
table_name	group_concat(t.table_schema, '.', t.table_name)	num1
ALL_PLUGINS	information_schema.ALL_PLUGINS	1
APPLICABLE_ROLES	information_schema.APPLICABLE_ROLES	1
CHARACTER_SETS	information_schema.CHARACTER_SETS	1
CLIENT_STATISTICS	information_schema.CLIENT_STATISTICS	1
COLLATIONS	information_schema.COLLATIONS	1
COLLATION_CHARACTER_SET_APPLICABILITY	information_schema.COLLATION_CHARACTER_SET_APPLICABILITY	1
COLUMNS	information_schema.COLUMNS	1
COLUMN_PRIVILEGES	information_schema.COLUMN_PRIVILEGES	1
ENGINES	information_schema.ENGINES	1
EVENTS	information_schema.EVENTS	1
FILES	information_schema.FILES	1
GEOMETRY_COLUMNS	information_schema.GEOMETRY_COLUMNS	1
GLOBAL_STATUS	information_schema.GLOBAL_STATUS	1
GLOBAL_VARIABLES	information_schema.GLOBAL_VARIABLES	1
INDEX_STATISTICS	information_schema.INDEX_STATISTICS	1
INNODB_BUFFER_PAGE	information_schema.INNODB_BUFFER_PAGE	1
INNODB_BUFFER_PAGE_LRU	information_schema.INNODB_BUFFER_PAGE_LRU	1
INNODB_BUFFER_POOL_STATS	information_schema.INNODB_BUFFER_POOL_STATS	1
INNODB_CMP	information_schema.INNODB_CMP	1
INNODB_CMPMEM	information_schema.INNODB_CMPMEM	1
INNODB_CMPMEM_RESET	information_schema.INNODB_CMPMEM_RESET	1
INNODB_CMP_PER_INDEX	information_schema.INNODB_CMP_PER_INDEX	1
INNODB_CMP_RESET	information_schema.INNODB_CMP_RESET	1
INNODB_LOCKS	information_schema.INNODB_LOCKS	1
INNODB_LOCK_WAITS	information_schema.INNODB_LOCK_WAITS	1
INNODB_METRICS	information_schema.INNODB_METRICS	1
INNODB_MUTEXES	information_schema.INNODB_MUTEXES	1
INNODB_SYS_COLUMNS	information_schema.INNODB_SYS_COLUMNS	1
INNODB_SYS_FIELDS	information_schema.INNODB_SYS_FIELDS	1
INNODB_SYS_FOREIGN	information_schema.INNODB_SYS_FOREIGN	1
INNODB_SYS_FOREIGN_COLS	information_schema.INNODB_SYS_FOREIGN_COLS	1
INNODB_SYS_INDEXES	information_schema.INNODB_SYS_INDEXES	1
INNODB_SYS_TABLES	information_schema.INNODB_SYS_TABLES	1
INNODB_SYS_TABLESTATS	information_schema.INNODB_SYS_TABLESTATS	1
INNODB_SYS_VIRTUAL	information_schema.INNODB_SYS_VIRTUAL	1
INNODB_TABLESPACES_ENCRYPTION	information_schema.INNODB_TABLESPACES_ENCRYPTION	1
INNODB_TABLESPACES_SCRUBBING	information_schema.INNODB_TABLESPACES_SCRUBBING	1
INNODB_TRX	information_schema.INNODB_TRX	1
KEY_CACHES	information_schema.KEY_CACHES	1
KEY_COLUMN_USAGE	information_schema.KEY_COLUMN_USAGE	1
PARAMETERS	information_schema.PARAMETERS	1
PARTITIONS	information_schema.PARTITIONS	1
PLUGINS	information_schema.PLUGINS	1
PROCESSLIST	information_schema.PROCESSLIST	1
PROFILING	information_schema.PROFILING	1
REFERENTIAL_CONSTRAINTS	information_schema.REFERENTIAL_CONSTRAINTS	1
ROUTINES	information_schema.ROUTINES	1
SCHEMATA	information_schema.SCHEMATA	1
SCHEMA_PRIVILEGES	information_schema.SCHEMA_PRIVILEGES	1
SESSION_STATUS	information_schema.SESSION_STATUS	1
SESSION_VARIABLES	information_schema.SESSION_VARIABLES	1
SPATIAL_REF_SYS	information_schema.SPATIAL_REF_SYS	1
STATISTICS	information_schema.STATISTICS	1
SYSTEM_VARIABLES	information_schema.SYSTEM_VARIABLES	1
TABLES	information_schema.TABLES	1
TABLESPACES	information_schema.TABLESPACES	1
TABLE_CONSTRAINTS	information_schema.TABLE_CONSTRAINTS	1
TABLE_PRIVILEGES	information_schema.TABLE_PRIVILEGES	1
TABLE_STATISTICS	information_schema.TABLE_STATISTICS	1
TRIGGERS	information_schema.TRIGGERS	1
USER_PRIVILEGES	information_schema.USER_PRIVILEGES	1
USER_STATISTICS	information_schema.USER_STATISTICS	1
VIEWS	information_schema.VIEWS	1
+---------------------------------------+
+---------------------------------------+
+---------------------------------------+
Database: information_schema
|                Tables                 |
| ALL_PLUGINS                           |
| APPLICABLE_ROLES                      |
| CHARACTER_SETS                        |
| CLIENT_STATISTICS                     |
| COLLATIONS                            |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS                               |
| COLUMN_PRIVILEGES                     |
| ENABLED_ROLES                         |
| ENGINES                               |
| EVENTS                                |
| FILES                                 |
| GEOMETRY_COLUMNS                      |
| GLOBAL_STATUS                         |
| GLOBAL_VARIABLES                      |
| INDEX_STATISTICS                      |
| INNODB_BUFFER_PAGE                    |
| INNODB_BUFFER_PAGE_LRU                |
| INNODB_BUFFER_POOL_STATS              |
| INNODB_CMP                            |
| INNODB_CMPMEM                         |
| INNODB_CMPMEM_RESET                   |
| INNODB_CMP_PER_INDEX                  |
| INNODB_CMP_RESET                      |
| INNODB_LOCKS                          |
| INNODB_LOCK_WAITS                     |
| INNODB_METRICS                        |
| INNODB_MUTEXES                        |
| INNODB_SYS_COLUMNS                    |
| INNODB_SYS_FIELDS                     |
| INNODB_SYS_FOREIGN                    |
| INNODB_SYS_FOREIGN_COLS               |
| INNODB_SYS_INDEXES                    |
| INNODB_SYS_TABLES                     |
| INNODB_SYS_TABLESTATS                 |
| INNODB_SYS_VIRTUAL                    |
| INNODB_TABLESPACES_ENCRYPTION         |
| INNODB_TABLESPACES_SCRUBBING          |
| INNODB_TRX                            |
| KEY_CACHES                            |
| KEY_COLUMN_USAGE                      |
| PARAMETERS                            |
| PARTITIONS                            |
| PLUGINS                               |
| PROCESSLIST                           |
| PROFILING                             |
| REFERENTIAL_CONSTRAINTS               |
| ROUTINES                              |
| SCHEMATA                              |
| SCHEMA_PRIVILEGES                     |
| SESSION_STATUS                        |
| SESSION_VARIABLES                     |
| SPATIAL_REF_SYS                       |
| STATISTICS                            |
| SYSTEM_VARIABLES                      |
| TABLES                                |
| TABLESPACES                           |
| TABLE_CONSTRAINTS                     |
| TABLE_PRIVILEGES                      |
| TABLE_STATISTICS                      |
| TRIGGERS                              |
| USER_PRIVILEGES                       |
| USER_STATISTICS                       |
| VIEWS                                 |
+---------------------------------------+
+---------------------------------------+
+---------------------------------------+
Database: INFORMATION_SCHEMA
|                Tables                 |
| ALL_PLUGINS                           |
| APPLICABLE_ROLES                      |
| CHARACTER_SETS                        |
| CLIENT_STATISTICS                     |
| COLLATIONS                            |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS                               |
| COLUMN_PRIVILEGES                     |
| ENABLED_ROLES                         |
| ENGINES                               |
| EVENTS                                |
| FILES                                 |
| GEOMETRY_COLUMNS                      |
| GLOBAL_STATUS                         |
| GLOBAL_VARIABLES                      |
| INDEX_STATISTICS                      |
| INNODB_BUFFER_PAGE                    |
| INNODB_BUFFER_PAGE_LRU                |
| INNODB_BUFFER_POOL_STATS              |
| INNODB_CMP                            |
| INNODB_CMPMEM                         |
| INNODB_CMPMEM_RESET                   |
| INNODB_CMP_PER_INDEX                  |
| INNODB_CMP_RESET                      |
| INNODB_LOCKS                          |
| INNODB_LOCK_WAITS                     |
| INNODB_METRICS                        |
| INNODB_MUTEXES                        |
| INNODB_SYS_COLUMNS                    |
| INNODB_SYS_FIELDS                     |
| INNODB_SYS_FOREIGN                    |
| INNODB_SYS_FOREIGN_COLS               |
| INNODB_SYS_INDEXES                    |
| INNODB_SYS_TABLES                     |
| INNODB_SYS_TABLESTATS                 |
| INNODB_SYS_VIRTUAL                    |
| INNODB_TABLESPACES_ENCRYPTION         |
| INNODB_TABLESPACES_SCRUBBING          |
| INNODB_TRX                            |
| KEY_CACHES                            |
| KEY_COLUMN_USAGE                      |
| PARAMETERS                            |
| PARTITIONS                            |
| PLUGINS                               |
| PROCESSLIST                           |
| PROFILING                             |
| REFERENTIAL_CONSTRAINTS               |
| ROUTINES                              |
| SCHEMATA                              |
| SCHEMA_PRIVILEGES                     |
| SESSION_STATUS                        |
| SESSION_VARIABLES                     |
| SPATIAL_REF_SYS                       |
| STATISTICS                            |
| SYSTEM_VARIABLES                      |
| TABLES                                |
| TABLESPACES                           |
| TABLE_CONSTRAINTS                     |
| TABLE_PRIVILEGES                      |
| TABLE_STATISTICS                      |
| TRIGGERS                              |
| USER_PRIVILEGES                       |
| USER_STATISTICS                       |
| VIEWS                                 |
+--------------------+
+--------------------+
+--------------------+
Wildcard: inf_rmation_schema
|     Databases      |
| information_schema |
SELECT table_schema, count(*) FROM information_schema.TABLES WHERE table_schema IN ('mysql', 'INFORMATION_SCHEMA', 'test', 'mysqltest') GROUP BY TABLE_SCHEMA;
table_schema	count(*)
information_schema	64
mysql	30