1
DROP TABLE IF EXISTS t0,t1,t2,t3,t4,t5;
2
select count(*) from data_dictionary.SCHEMAS where schema_name > 'm';
5
select count(*) from data_dictionary.schemas;
8
show databases like 't%';
17
create database mysqltest;
18
create table mysqltest.t1(a int, b VARCHAR(30), KEY string_data (b));
19
create table test.t2(a int);
20
create table t3(a int, KEY a_data (a));
21
create table mysqltest.t4(a int);
22
create table t5 (id int auto_increment primary key);
23
insert into t5 values (10);
24
select table_name from data_dictionary.TABLES
25
where table_schema = "mysqltest" and table_name like "t%";
29
show tables like 't%';
35
Session Schema Name Type Engine Version Rows Avg_row_length Table_size Auto_increment
36
# test t5 STANDARD PBXT # # # # #
37
show columns from t3 like "a%";
38
Field Type Null Default Default_is_NULL On_Update
40
select * from data_dictionary.COLUMNS where table_name="t1"
42
TABLE_SCHEMA TABLE_NAME COLUMN_NAME COLUMN_TYPE ORDINAL_POSITION COLUMN_DEFAULT COLUMN_DEFAULT_IS_NULL COLUMN_DEFAULT_UPDATE IS_NULLABLE IS_INDEXED IS_USED_IN_PRIMARY IS_UNIQUE IS_MULTI IS_FIRST_IN_MULTI INDEXES_FOUND_IN DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE COLLATION_NAME COLUMN_COMMENT
43
mysqltest t1 a INTEGER 0 NULL YES YES NO NO NO NO NO 0 INTEGER 0 0 0 0 NULL
44
select table_name, column_name from data_dictionary.columns
45
where table_schema = 'mysqltest' and table_name = 't1';
46
table_name column_name
49
show columns from mysqltest.t1;
50
Field Type Null Default Default_is_NULL On_Update
53
drop tables mysqltest.t4, mysqltest.t1, t2, t3, t5;
54
drop database mysqltest;
55
select * from data_dictionary.CHARACTER_SETS
56
where CHARACTER_SET_NAME like 'latin1%';
57
CHARACTER_SET_NAME DEFAULT_COLLATE_NAME DESCRIPTION MAXLEN
58
select * from data_dictionary.COLLATIONS
59
where COLLATION_NAME like 'latin1%';
60
CHARACTER_SET_NAME COLLATION_NAME DESCRIPTION ID IS_DEFAULT IS_COMPILED SORTLEN
61
select DATA_TYPE from data_dictionary.columns
62
where table_schema="data_dictionary" and table_name="COLUMNS" and
63
(column_name="character_set_name" or column_name="collation_name");
66
select TABLE_TYPE from data_dictionary.tables where
67
table_schema="data_dictionary" and table_name="COLUMNS";
70
select table_type from data_dictionary.tables
71
where table_schema="mysql" and table_name="user";
73
select table_schema,table_name, column_name from
74
data_dictionary.columns
75
where DATA_TYPE = 'longtext';
76
table_schema table_name column_name
77
select table_name, column_name, DATA_TYPE from data_dictionary.columns
78
where DATA_TYPE = 'datetime';
79
table_name column_name DATA_TYPE
80
SELECT COUNT(*) FROM DATA_DICTIONARY.TABLES A
82
(SELECT * FROM DATA_DICTIONARY.COLUMNS B
83
WHERE A.TABLE_SCHEMA = B.TABLE_SCHEMA
84
AND A.TABLE_NAME = B.TABLE_NAME);
91
x_decimal DECIMAL(5,3),
92
x_numeric NUMERIC(5,3),
95
x_double_precision DOUBLE PRECISION );
96
SELECT COLUMN_NAME, CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH
97
FROM DATA_DICTIONARY.COLUMNS
98
WHERE TABLE_NAME= 't1';
99
COLUMN_NAME CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH
102
x_double_precision 0 0
109
SELECT table_schema, count(*) FROM data_dictionary.TABLES
110
WHERE table_name NOT LIKE 'ndb_%' AND
111
table_name NOT LIKE 'falcon%' AND
113
GROUP BY TABLE_SCHEMA;
114
table_schema count(*)
115
create table t1(f1 LONGBLOB, f2 LONGTEXT);
116
select column_name, DATA_TYPE, CHARACTER_OCTET_LENGTH,
117
CHARACTER_MAXIMUM_LENGTH
118
from data_dictionary.columns
119
where table_name='t1';
120
column_name DATA_TYPE CHARACTER_OCTET_LENGTH CHARACTER_MAXIMUM_LENGTH
124
create table t1(f1 int, f2 int, f3 BIGINT, f4 int,
125
f5 BIGINT, f6 int, f7 int);
126
select column_name, NUMERIC_PRECISION, NUMERIC_SCALE
127
from data_dictionary.columns
128
where table_name='t1';
129
column_name NUMERIC_PRECISION NUMERIC_SCALE
138
create table t1 (a int not null, b int);
140
select column_name, column_default,column_default_is_null from columns
141
where table_schema='test' and table_name='t1';
142
column_name column_default column_default_is_null
146
show columns from t1;
147
Field Type Null Default Default_is_NULL On_Update
151
alter database data_dictionary;
152
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near '' at line 1
155
create temporary table schemas(f1 char(10));
156
ERROR 42000: Access denied for user ''@'' to database 'data_dictionary'
158
create table t1(id int);
159
insert into t1(id) values (1);
160
select 1 from (select 1 from test.t1) a;
164
select 1 from (select 1 from test.t1) a;
169
create temporary table t1(f1 int, index(f1));
170
show columns from t1;
171
Field Type Null Default Default_is_NULL On_Update
174
Field Type Null Default Default_is_NULL On_Update
176
show indexes from t1;
177
Table Unique Key_name Seq_in_index Column_name
180
create table t1(f1 varbinary(32), f2 varbinary(64));
181
select character_maximum_length, character_octet_length
182
from data_dictionary.columns where table_name='t1';
183
character_maximum_length character_octet_length
187
select DATA_TYPE, group_concat(table_schema, '.', table_name) as a, count(*) as num
188
from data_dictionary.columns where
189
table_schema='data_dictionary' and
190
(DATA_TYPE = 'varchar' or DATA_TYPE = 'varchar'
191
or DATA_TYPE = 'varchar')
192
group by DATA_TYPE order by DATA_TYPE, num;
196
Warning # 1 line(s) were cut by GROUP_CONCAT()
197
create table t1(f1 char(1) not null, f2 char(9) not null);
198
select CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH from
199
data_dictionary.columns where table_schema='test' and table_name = 't1';
200
CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH
205
create table t1(f1 char(5));
206
create table t2(f1 char(5));
207
select concat(@a, table_name), @a, table_name
208
from data_dictionary.tables where table_schema = 'test';
209
concat(@a, table_name) @a table_name
213
SELECT t.table_name, c1.column_name
214
FROM data_dictionary.tables t
216
data_dictionary.columns c1
217
ON t.table_schema = c1.table_schema AND
218
t.table_name = c1.table_name
219
WHERE t.table_schema = 'data_dictionary' AND
220
c1.ordinal_position =
221
( SELECT COALESCE(MIN(c2.ordinal_position),1)
222
FROM data_dictionary.columns c2
223
WHERE c2.table_schema = t.table_schema AND
224
c2.table_name = t.table_name AND
225
c2.column_name LIKE '%SCHEMA%'
227
AND t.table_name NOT LIKE 'falcon%'
228
AND t.ENGINE IS NULL;
229
table_name column_name
230
SELECT t.table_name, c1.column_name
231
FROM data_dictionary.tables t
233
data_dictionary.columns c1
234
ON t.table_schema = c1.table_schema AND
235
t.table_name = c1.table_name
236
WHERE t.table_schema = 'data_dictionary' AND
237
c1.ordinal_position =
238
( SELECT COALESCE(MIN(c2.ordinal_position),1)
239
FROM data_dictionary.columns c2
240
WHERE c2.table_schema = 'data_dictionary' AND
241
c2.table_name = t.table_name AND
242
c2.column_name LIKE '%SCHEMA%'
244
AND t.table_name NOT LIKE 'falcon%'
245
AND t.ENGINE IS NULL;
246
table_name column_name
247
SELECT MAX(table_name) FROM data_dictionary.tables;
250
SELECT table_name from data_dictionary.tables
251
WHERE table_name=(SELECT MAX(table_name)
252
FROM data_dictionary.tables);
255
create table t1 (f1 int);
256
create table t2 (f1 int, f2 int);
257
select table_name from data_dictionary.tables
258
where table_schema = 'test' and table_name not in
259
(select table_name from data_dictionary.columns
260
where table_schema = 'test' and column_name = 'f3');
265
select 1 as f1 from data_dictionary.tables where "CHARACTER_SETS"=
266
(select cast(table_name as char) from data_dictionary.tables
267
order by table_name limit 1) limit 1;
270
select t.table_name, group_concat(t.table_schema, '.', t.table_name),
272
from data_dictionary.tables t
273
inner join data_dictionary.columns c1
274
on t.table_schema = c1.table_schema AND t.table_name = c1.table_name
275
where t.table_schema = 'data_dictionary' AND
276
t.table_name not like 'falcon%' AND
278
c1.ordinal_position =
279
(select isnull(c2.DATA_TYPE) -
280
isnull(group_concat(c2.table_schema, '.', c2.table_name)) +
282
from data_dictionary.columns c2 where
283
c2.table_schema='data_dictionary' and
284
(c2.DATA_TYPE = 'varchar' or c2.DATA_TYPE = 'varchar')
285
group by c2.DATA_TYPE order by num limit 1)
286
group by t.table_name order by num1, t.table_name;
287
table_name group_concat(t.table_schema, '.', t.table_name) num1
289
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near '' at line 1
291
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near '' at line 1
294
f2 varchar(50) not null,
295
f3 varchar(50) default '',
296
f4 varchar(50) default NULL,
298
f6 bigint not null default 10,
299
f7 datetime not null,
300
f8 datetime default '2006-01-01'
302
select column_name,column_default,column_default_is_null from data_dictionary.columns where table_name= 't1';
303
column_name column_default column_default_is_null
311
f8 2006-01-01 00:00:00 NO
312
show columns from t1;
313
Field Type Null Default Default_is_NULL On_Update
321
f8 DATETIME YES 2006-01-01 00:00:00 NO
323
SET max_heap_table_size = DEFAULT;
326
SELECT SCHEMA_NAME FROM DATA_DICTIONARY.SCHEMAS
327
WHERE SCHEMA_NAME ='data_dictionary';
330
SELECT TABLE_COLLATION FROM DATA_DICTIONARY.TABLES
331
WHERE TABLE_SCHEMA='mysql' and TABLE_NAME= 'db';
333
select * from data_dictionary.columns where table_schema = NULL;
334
TABLE_SCHEMA TABLE_NAME COLUMN_NAME COLUMN_TYPE ORDINAL_POSITION COLUMN_DEFAULT COLUMN_DEFAULT_IS_NULL COLUMN_DEFAULT_UPDATE IS_NULLABLE IS_INDEXED IS_USED_IN_PRIMARY IS_UNIQUE IS_MULTI IS_FIRST_IN_MULTI INDEXES_FOUND_IN DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE COLLATION_NAME COLUMN_COMMENT
335
select * from `data_dictionary`.`COLUMNS` where `TABLE_NAME` = NULL;
336
TABLE_SCHEMA TABLE_NAME COLUMN_NAME COLUMN_TYPE ORDINAL_POSITION COLUMN_DEFAULT COLUMN_DEFAULT_IS_NULL COLUMN_DEFAULT_UPDATE IS_NULLABLE IS_INDEXED IS_USED_IN_PRIMARY IS_UNIQUE IS_MULTI IS_FIRST_IN_MULTI INDEXES_FOUND_IN DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE COLLATION_NAME COLUMN_COMMENT
337
select * from `data_dictionary`.`INDEXES` where `TABLE_SCHEMA` = NULL;
338
TABLE_SCHEMA TABLE_NAME INDEX_NAME IS_USED_IN_PRIMARY IS_UNIQUE IS_NULLABLE KEY_LENGTH INDEX_TYPE INDEX_COMMENT
339
select * from `data_dictionary`.`INDEXES` where `TABLE_NAME` = NULL;
340
TABLE_SCHEMA TABLE_NAME INDEX_NAME IS_USED_IN_PRIMARY IS_UNIQUE IS_NULLABLE KEY_LENGTH INDEX_TYPE INDEX_COMMENT
341
select * from data_dictionary.schemas where schema_name = NULL;
342
SCHEMA_NAME DEFAULT_COLLATION_NAME SCHEMA_CREATION_TIME SCHEMA_UPDATE_TIME
343
select * from data_dictionary.tables where table_schema = NULL;
344
TABLE_SCHEMA TABLE_NAME TABLE_TYPE ENGINE ROW_FORMAT TABLE_COLLATION TABLE_CREATION_TIME TABLE_UPDATE_TIME TABLE_COMMENT
345
select * from data_dictionary.tables where table_name = NULL;
346
TABLE_SCHEMA TABLE_NAME TABLE_TYPE ENGINE ROW_FORMAT TABLE_COLLATION TABLE_CREATION_TIME TABLE_UPDATE_TIME TABLE_COMMENT
348
# Test that the query is visible to self and others.
350
SELECT info FROM data_dictionary.processlist WHERE id = CONNECTION_ID();
352
SELECT info FROM data_dictionary.processlist WHERE id = CONNECTION_ID()
353
SELECT * FROM data_dictionary.character_sets ORDER BY character_set_name;
354
CHARACTER_SET_NAME DEFAULT_COLLATE_NAME DESCRIPTION MAXLEN
357
SELECT * FROM data_dictionary.collations ORDER BY collation_name;
358
CHARACTER_SET_NAME COLLATION_NAME DESCRIPTION ID IS_DEFAULT IS_COMPILED SORTLEN
359
binary binary binary 63 YES YES 1
360
utf8_general_ci utf8_bin utf8 46 NO YES 1
361
utf8_general_ci utf8_czech_ci utf8 234 NO YES 8
362
utf8_general_ci utf8_danish_ci utf8 235 NO YES 8
363
utf8_general_ci utf8_esperanto_ci utf8 241 NO YES 8
364
utf8_general_ci utf8_estonian_ci utf8 230 NO YES 8
365
utf8_general_ci utf8_general_ci utf8 45 YES YES 1
366
utf8_general_ci utf8_hungarian_ci utf8 242 NO YES 8
367
utf8_general_ci utf8_icelandic_ci utf8 225 NO YES 8
368
utf8_general_ci utf8_latvian_ci utf8 226 NO YES 8
369
utf8_general_ci utf8_lithuanian_ci utf8 236 NO YES 8
370
utf8_general_ci utf8_persian_ci utf8 240 NO YES 8
371
utf8_general_ci utf8_polish_ci utf8 229 NO YES 8
372
utf8_general_ci utf8_romanian_ci utf8 227 NO YES 8
373
utf8_general_ci utf8_roman_ci utf8 239 NO YES 8
374
utf8_general_ci utf8_sinhala_ci utf8 243 NO YES 8
375
utf8_general_ci utf8_slovak_ci utf8 237 NO YES 8
376
utf8_general_ci utf8_slovenian_ci utf8 228 NO YES 8
377
utf8_general_ci utf8_spanish2_ci utf8 238 NO YES 8
378
utf8_general_ci utf8_spanish_ci utf8 231 NO YES 8
379
utf8_general_ci utf8_swedish_ci utf8 232 NO YES 8
380
utf8_general_ci utf8_turkish_ci utf8 233 NO YES 8
381
utf8_general_ci utf8_unicode_ci utf8 224 NO YES 8
382
SELECT table_name, column_name
383
FROM data_dictionary.columns
385
(SELECT table_name FROM data_dictionary.tables
386
WHERE ENGINE IS NULL)
388
table_name column_name
389
SELECT count(schema_name) FROM data_dictionary.schemas ORDER BY schema_name;
392
SELECT count(*) FROM data_dictionary.session_status ORDER BY variable_name;
395
SELECT count(*) FROM data_dictionary.session_variables ORDER BY variable_name;
398
SELECT count(*) FROM data_dictionary.global_status ORDER BY variable_name;
401
SELECT count(*) FROM data_dictionary.global_variables ORDER BY variable_name;
404
SELECT table_schema, table_name FROM data_dictionary.tables WHERE ENGINE IS NULL ORDER BY table_name;
405
table_schema table_name
406
SELECT count(*) FROM data_dictionary.plugins;