~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

t2

t3

t5

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 * 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

TABLES  CREATE_TIME     datetime

TABLES  UPDATE_TIME     datetime

TABLES  CHECK_TIME      datetime

SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES A

WHERE NOT EXISTS 

(SELECT * FROM INFORMATION_SCHEMA.COLUMNS B

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

SELECT table_schema, count(*) FROM information_schema.TABLES

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

table_schema    count(*)

show create database information_schema;

Database        Create Database

information_schema      CREATE DATABASE `information_schema` /*!40100 DEFAULT CHARACTER SET utf8 */

f1      blob    4294967295      4294967295

f2      text    4294967295      4294967295

drop table t1;

select column_name, NUMERIC_PRECISION, NUMERIC_SCALE

from information_schema.columns

where table_name='t1';

column_name     NUMERIC_PRECISION       NUMERIC_SCALE

f3      19      0

f4      10      0

f5      19      0

drop table t1;

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

use information_schema;

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

varchar(20)     information_schema.PLUGINS      1

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

STATISTICS      TABLE_SCHEMA

TABLES  TABLE_SCHEMA

TABLE_CONSTRAINTS       CONSTRAINT_SCHEMA

SELECT t.table_name, c1.column_name

FROM information_schema.tables t

INNER JOIN

STATISTICS      TABLE_SCHEMA

TABLES  TABLE_SCHEMA

TABLE_CONSTRAINTS       CONSTRAINT_SCHEMA

SELECT MAX(table_name) FROM information_schema.tables;

MAX(table_name)

TABLE_CONSTRAINTS

COLUMNS information_schema.COLUMNS      1

GLOBAL_STATUS   information_schema.GLOBAL_STATUS        1

GLOBAL_VARIABLES        information_schema.GLOBAL_VARIABLES     1

KEY_COLUMN_USAGE        information_schema.KEY_COLUMN_USAGE     1

PLUGINS information_schema.PLUGINS      1

PROCESSLIST     information_schema.PROCESSLIST  1