22
22
show columns from t3 like "a%";
23
23
Field Type Null Default Default is NULL On Update
24
select * from information_schema.old_COLUMNS where table_name="t1"
24
select * from data_dictionary.columns where table_name="t1"
25
25
and column_name= "a";
26
TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME ORDINAL_POSITION COLUMN_DEFAULT IS_NULLABLE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE CHARACTER_SET_NAME COLLATION_NAME COLUMN_TYPE COLUMN_KEY EXTRA PRIVILEGES COLUMN_COMMENT STORAGE FORMAT
27
NULL mysqltest t1 a 1 NULL YES int NULL NULL 10 0 NULL NULL int Default Default
28
select table_name, column_name, privileges from information_schema.old_columns
26
TABLE_SCHEMA TABLE_NAME COLUMN_NAME COLUMN_TYPE ORDINAL_POSITION COLUMN_DEFAULT COLUMN_DEFAULT_IS_NULL COLUMN_DEFAULT_UPDATE IS_NULLABLE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE COLLATION_NAME COLUMN_COMMENT
27
mysqltest t1 a INTEGER 0 0 TRUE TRUE INTEGER 0 0 0 0 utf8_general_ci
28
select table_name, column_name from data_dictionary.columns
29
29
where table_schema = 'mysqltest' and table_name = 't1';
30
table_name column_name privileges
30
table_name column_name
33
33
show columns from mysqltest.t1;
34
34
Field Type Null Default Default is NULL On Update
35
35
a INTEGER TRUE TRUE
38
38
drop database mysqltest;
39
39
select * from information_schema.old_table_names;
40
40
ERROR 42S02: Unknown table 'old_table_names' in information_schema
41
select column_type from information_schema.old_columns
41
select column_type from data_dictionary.columns
42
42
where table_schema="information_schema" and table_name="COLUMNS" and
43
43
(column_name="character_set_name" or column_name="collation_name");
90
90
table_name NOT LIKE 'falcon%'
91
91
GROUP BY TABLE_SCHEMA ORDER BY table_schema;
92
92
table_schema count(*)
94
94
show create database information_schema;
95
95
Database Create Database
96
96
information_schema CREATE DATABASE `information_schema`
97
97
create table t1(f1 LONGBLOB, f2 LONGTEXT);
98
98
select column_name,data_type,CHARACTER_OCTET_LENGTH,
99
99
CHARACTER_MAXIMUM_LENGTH
100
from information_schema.old_columns
100
from data_dictionary.columns
101
101
where table_name='t1';
102
102
column_name data_type CHARACTER_OCTET_LENGTH CHARACTER_MAXIMUM_LENGTH
103
f1 blob 4294967295 4294967295
104
f2 text 4294967295 4294967295
106
106
create table t1(f1 int, f2 int, f3 BIGINT, f4 int,
107
107
f5 BIGINT, f6 int, f7 int);
108
108
select column_name, NUMERIC_PRECISION, NUMERIC_SCALE
109
from information_schema.old_columns
109
from data_dictionary.columns
110
110
where table_name='t1';
111
111
column_name NUMERIC_PRECISION NUMERIC_SCALE
120
120
create table t1 (a int not null, b int);
121
121
use information_schema;
122
select column_name, column_default from information_schema.old_columns
122
select column_name, column_default from data_dictionary.columns
123
123
where table_schema='test' and table_name='t1';
124
124
column_name column_default
128
128
show columns from t1;
129
129
Field Type Null Default Default is NULL On Update
157
157
create table t1(f1 varbinary(32), f2 varbinary(64));
158
158
select character_maximum_length, character_octet_length
159
from information_schema.old_columns where table_name='t1';
159
from data_dictionary.columns where table_name='t1';
160
160
character_maximum_length character_octet_length
164
164
select column_type, group_concat(table_schema, '.', table_name), count(*) as num
165
from information_schema.old_columns where
165
from data_dictionary.columns where
166
166
table_schema='information_schema' and
167
167
(column_type = 'varchar(7)' or column_type = 'varchar(20)'
168
168
or column_type = 'varchar(27)')
169
169
group by column_type order by column_type, num;
170
170
column_type group_concat(table_schema, '.', table_name) num
171
varchar(27) information_schema.OLD_COLUMNS 1
172
171
create table t1(f1 char(1) not null, f2 char(9) not null);
173
172
select CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH from
174
information_schema.old_columns where table_schema='test' and table_name = 't1';
173
data_dictionary.columns where table_schema='test' and table_name = 't1';
175
174
CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH
188
187
SELECT t.table_name, c1.column_name
189
188
FROM data_dictionary.tables t
191
information_schema.old_columns c1
190
data_dictionary.columns c1
192
191
ON t.table_schema = c1.table_schema AND
193
192
t.table_name = c1.table_name
194
193
WHERE t.table_schema = 'information_schema' AND
195
194
c1.ordinal_position =
196
195
( SELECT COALESCE(MIN(c2.ordinal_position),1)
197
FROM information_schema.old_columns c2
196
FROM data_dictionary.columns c2
198
197
WHERE c2.table_schema = t.table_schema AND
199
198
c2.table_name = t.table_name AND
200
199
c2.column_name LIKE '%SCHEMA%'
202
201
AND t.table_name NOT LIKE 'falcon%'
203
202
ORDER BY t.table_name, c1.column_name;
204
203
table_name column_name
206
INNODB_CMPMEM page_size
207
INNODB_CMPMEM_RESET page_size
208
INNODB_CMP_RESET page_size
210
INNODB_LOCK_WAITS requesting_trx_id
212
OLD_COLUMNS TABLE_SCHEMA
204
INNODB_CMP compress_ops
205
INNODB_CMPMEM pages_used
206
INNODB_CMPMEM_RESET pages_used
207
INNODB_CMP_RESET compress_ops
208
INNODB_LOCKS lock_trx_id
209
INNODB_LOCK_WAITS requested_lock_id
213
211
OLD_KEY_COLUMN_USAGE CONSTRAINT_SCHEMA
214
212
OLD_REFERENTIAL_CONSTRAINTS CONSTRAINT_SCHEMA
215
213
OLD_STATISTICS TABLE_SCHEMA
217
215
SELECT t.table_name, c1.column_name
218
216
FROM data_dictionary.tables t
220
information_schema.old_columns c1
218
data_dictionary.columns c1
221
219
ON t.table_schema = c1.table_schema AND
222
220
t.table_name = c1.table_name
223
221
WHERE t.table_schema = 'information_schema' AND
224
222
c1.ordinal_position =
225
223
( SELECT COALESCE(MIN(c2.ordinal_position),1)
226
FROM information_schema.old_columns c2
224
FROM data_dictionary.columns c2
227
225
WHERE c2.table_schema = 'information_schema' AND
228
226
c2.table_name = t.table_name AND
229
227
c2.column_name LIKE '%SCHEMA%'
231
229
AND t.table_name NOT LIKE 'falcon%'
232
230
ORDER BY t.table_name, c1.column_name;
233
231
table_name column_name
235
INNODB_CMPMEM page_size
236
INNODB_CMPMEM_RESET page_size
237
INNODB_CMP_RESET page_size
239
INNODB_LOCK_WAITS requesting_trx_id
241
OLD_COLUMNS TABLE_SCHEMA
232
INNODB_CMP compress_ops
233
INNODB_CMPMEM pages_used
234
INNODB_CMPMEM_RESET pages_used
235
INNODB_CMP_RESET compress_ops
236
INNODB_LOCKS lock_trx_id
237
INNODB_LOCK_WAITS requested_lock_id
242
239
OLD_KEY_COLUMN_USAGE CONSTRAINT_SCHEMA
243
240
OLD_REFERENTIAL_CONSTRAINTS CONSTRAINT_SCHEMA
244
241
OLD_STATISTICS TABLE_SCHEMA
262
259
select t.table_name, group_concat(t.table_schema, '.', t.table_name),
264
261
from data_dictionary.tables t
265
inner join information_schema.old_columns c1
262
inner join data_dictionary.columns c1
266
263
on t.table_schema = c1.table_schema AND t.table_name = c1.table_name
267
264
where t.table_schema = 'information_schema' AND
268
265
t.table_name not like 'falcon%' AND
270
267
(select isnull(c2.column_type) -
271
268
isnull(group_concat(c2.table_schema, '.', c2.table_name)) +
273
from information_schema.old_columns c2 where
270
from data_dictionary.columns c2 where
274
271
c2.table_schema='information_schema' and
275
272
(c2.column_type = 'varchar(7)' or c2.column_type = 'varchar(20)')
276
273
group by c2.column_type order by num limit 1)
312
309
WHERE TABLE_SCHEMA='mysql' and TABLE_NAME= 'db';
314
311
select * from data_dictionary.columns where table_schema = NULL;
315
TABLE_SCHEMA TABLE_NAME COLUMN_NAME ORDINAL_POSITION COLUMN_DEFAULT COLUMN_DEFAULT_IS_NULL COLUMN_DEFAULT_UPDATE IS_NULLABLE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE COLLATION_NAME COLUMN_COMMENT
312
TABLE_SCHEMA TABLE_NAME COLUMN_NAME COLUMN_TYPE ORDINAL_POSITION COLUMN_DEFAULT COLUMN_DEFAULT_IS_NULL COLUMN_DEFAULT_UPDATE IS_NULLABLE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE COLLATION_NAME COLUMN_COMMENT
316
313
select * from `data_dictionary`.`columns` where `TABLE_NAME` = NULL;
317
TABLE_SCHEMA TABLE_NAME COLUMN_NAME ORDINAL_POSITION COLUMN_DEFAULT COLUMN_DEFAULT_IS_NULL COLUMN_DEFAULT_UPDATE IS_NULLABLE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE COLLATION_NAME COLUMN_COMMENT
314
TABLE_SCHEMA TABLE_NAME COLUMN_NAME COLUMN_TYPE ORDINAL_POSITION COLUMN_DEFAULT COLUMN_DEFAULT_IS_NULL COLUMN_DEFAULT_UPDATE IS_NULLABLE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE COLLATION_NAME COLUMN_COMMENT
318
315
select * from `information_schema`.`OLD_KEY_COLUMN_USAGE` where `TABLE_SCHEMA` = NULL;
319
316
CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME ORDINAL_POSITION POSITION_IN_UNIQUE_CONSTRAINT REFERENCED_TABLE_SCHEMA REFERENCED_TABLE_NAME REFERENCED_COLUMN_NAME
320
317
select * from `information_schema`.`OLD_KEY_COLUMN_USAGE` where `TABLE_NAME` = NULL;