~drizzle-trunk/drizzle/development

DROP TABLE IF EXISTS t0,t1,t2,t3,t4,t5;

select * from information_schema.SCHEMATA where schema_name > 'm';

CATALOG_NAME    SCHEMA_NAME     DEFAULT_CHARACTER_SET_NAME      DEFAULT_COLLATION_NAME  SQL_PATH

select schema_name from information_schema.schemata;

schema_name

information_schema

information_schema

mysql

test

create database mysqltest;

create table mysqltest.t1(a int, b VARCHAR(30), KEY string_data (b));

create table test.t2(a int);

t4

select * from information_schema.STATISTICS where TABLE_SCHEMA = "mysqltest";

TABLE_CATALOG   TABLE_SCHEMA    TABLE_NAME      NON_UNIQUE      INDEX_SCHEMA    INDEX_NAME      SEQ_IN_INDEX    COLUMN_NAME     COLLATION       CARDINALITY     SUB_PART        PACKED  NULLABLE        INDEX_TYPE      COMMENT INDEX_COMMENT

show tables like 't%';

Tables_in_test (t%)

t2

t5

show table status;

Name    Engine  Version Row_format      Rows    Avg_row_length  Data_length     Max_data_length Index_length    Data_free       Auto_increment  Create_time     Update_time     Check_time      Collation       Checksum        Create_options  Comment

show full columns from t3 like "a%";

Field   Type    Collation       Null    Key     Default Extra   Privileges      Comment

select * from information_schema.COLUMNS where table_name="t1"

and column_name= "a";

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

select table_name, column_name, privileges from information_schema.columns 

where table_schema = 'mysqltest' and table_name = 't1';

table_name      column_name     privileges

t1      b       

show columns from mysqltest.t1;

Field   Type    Null    Key     Default Extra

b       varchar(30)     YES     MUL     NULL    

drop tables mysqltest.t4, mysqltest.t1, t2, t3, t5;

drop database mysqltest;

select * from information_schema.CHARACTER_SETS

where CHARACTER_SET_NAME like 'latin1%';

CHARACTER_SET_NAME      DEFAULT_COLLATE_NAME    DESCRIPTION     MAXLEN

select * from information_schema.COLLATIONS

where COLLATION_NAME like 'latin1%';

COLLATION_NAME  CHARACTER_SET_NAME      ID      IS_DEFAULT      IS_COMPILED     SORTLEN

select * from information_schema.COLLATION_CHARACTER_SET_APPLICABILITY

where COLLATION_NAME like 'latin1%';

COLLATION_NAME  CHARACTER_SET_NAME

select * from information_schema.table_names;

ERROR 42S02: Unknown table 'table_names' in information_schema

select column_type from information_schema.columns

select table_type from information_schema.tables

where table_schema="mysql" and table_name="user";

table_type

show global status like "Threads_running";

Variable_name   Value

Threads_running #

information_schema.columns 

where data_type = 'longtext';

table_schema    table_name      column_name

select table_name, column_name, data_type from information_schema.columns

where data_type = 'datetime';

table_name      column_name     data_type

create table t1

( x_bigint BIGINT,

x_integer INTEGER,

x_decimal DECIMAL(5,3),

x_numeric NUMERIC(5,3),

x_real REAL,

COLUMN_NAME     CHARACTER_MAXIMUM_LENGTH        CHARACTER_OCTET_LENGTH

x_bigint        NULL    NULL

x_integer       NULL    NULL

x_decimal       NULL    NULL

x_numeric       NULL    NULL

x_real  NULL    NULL

WHERE table_name NOT LIKE 'ndb_%' AND table_name NOT LIKE 'falcon%' GROUP BY TABLE_SCHEMA;

table_schema    count(*)

information_schema      16

show create database information_schema;

Database        Create Database

create table t1(f1 LONGBLOB, f2 LONGTEXT);

select column_name,data_type,CHARACTER_OCTET_LENGTH,

CHARACTER_MAXIMUM_LENGTH

from information_schema.columns

where table_name='t1';

column_name     data_type       CHARACTER_OCTET_LENGTH  CHARACTER_MAXIMUM_LENGTH

drop table t1;

select column_name, NUMERIC_PRECISION, NUMERIC_SCALE

from information_schema.columns

where table_name='t1';

column_name     NUMERIC_PRECISION       NUMERIC_SCALE

f4      10      0

f5      19      0

drop table t1;

create table t1 (a int not null, b int);

use information_schema;

use test;

show columns from t1;

Field   Type    Null    Key     Default Extra

drop table t1;

alter database information_schema;

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

drop database information_schema;

create temporary table t1(f1 int, index(f1));

show columns from t1;

Field   Type    Null    Key     Default Extra

describe t1;

Field   Type    Null    Key     Default Extra

show indexes from t1;

Table   Non_unique      Key_name        Seq_in_index    Column_name     Collation       Cardinality     Sub_part        Packed  Null    Index_type      Comment Index_Comment

drop table t1;

select character_maximum_length, character_octet_length

from information_schema.columns where table_name='t1';

character_maximum_length        character_octet_length

 or column_type = 'varchar(27)')

group by column_type order by num;

column_type     group_concat(table_schema, '.', table_name)     num

varchar(27)     information_schema.COLUMNS      1

select CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH from

information_schema.columns where table_schema='test' and table_name = 't1';

CHARACTER_MAXIMUM_LENGTH        CHARACTER_OCTET_LENGTH

TABLE_CONSTRAINTS       CONSTRAINT_SCHEMA

SELECT MAX(table_name) FROM information_schema.tables;

MAX(table_name)

SELECT table_name from information_schema.tables

WHERE table_name=(SELECT MAX(table_name)

FROM information_schema.tables);

table_name

select table_name from information_schema.tables

where table_schema = 'test' and table_name not in

(select table_name from information_schema.columns

table_name      group_concat(t.table_schema, '.', t.table_name) num1

CHARACTER_SETS  information_schema.CHARACTER_SETS       1

COLLATIONS      information_schema.COLLATIONS   1

COLUMNS information_schema.COLUMNS      1

KEY_COLUMN_USAGE        information_schema.KEY_COLUMN_USAGE     1

PLUGINS information_schema.PLUGINS      1

PROCESSLIST     information_schema.PROCESSLIST  1

REFERENTIAL_CONSTRAINTS information_schema.REFERENTIAL_CONSTRAINTS      1

SCHEMATA        information_schema.SCHEMATA     1

STATISTICS      information_schema.STATISTICS   1

TABLES  information_schema.TABLES       1

TABLE_CONSTRAINTS       information_schema.TABLE_CONSTRAINTS    1

f2      varchar(50)     NO              NULL    

f3      varchar(50)     YES             NULL    

f4      varchar(50)     YES             NULL    

f7      datetime        NO              NULL    

f8      datetime        YES             NULL    

drop table t1;

SELECT TABLE_COLLATION FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_SCHEMA='mysql' and TABLE_NAME= 'db';

TABLE_COLLATION

select * from information_schema.columns where table_schema = NULL;

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

select * from `information_schema`.`COLUMNS` where `TABLE_NAME` = NULL;