path: root/mysql-test/r/information_schema.result

show variables where variable_name like "skip_show_database";
Variable_name	Value
skip_show_database	OFF
grant select, update, execute on test.* to mysqltest_2@localhost;
grant select, update on test.* to mysqltest_1@localhost;
create user mysqltest_3@localhost;
create user mysqltest_3;
select * from information_schema.SCHEMATA where schema_name > 'm';
CATALOG_NAME	SCHEMA_NAME	DEFAULT_CHARACTER_SET_NAME	DEFAULT_COLLATION_NAME	SQL_PATH
NULL	mysql	latin1	latin1_swedish_ci	NULL
NULL	test	latin1	latin1_swedish_ci	NULL
select schema_name from information_schema.schemata;
schema_name
information_schema
mysql
test
show databases like 't%';
Database (t%)
test
show databases;
Database
information_schema
mysql
test
show databases where `database` = 't%';
Database
create database mysqltest;
create table mysqltest.t1(a int, b VARCHAR(30), KEY string_data (b));
create table test.t2(a int);
create table t3(a int, KEY a_data (a));
create table mysqltest.t4(a int);
create view v1 (c) as select table_name from information_schema.TABLES;
select * from v1;
c
SCHEMATA
TABLES
COLUMNS
CHARACTER_SETS
COLLATIONS
COLLATION_CHARACTER_SET_APPLICABILITY
ROUTINES
STATISTICS
VIEWS
USER_PRIVILEGES
SCHEMA_PRIVILEGES
TABLE_PRIVILEGES
COLUMN_PRIVILEGES
TABLE_CONSTRAINTS
KEY_COLUMN_USAGE
columns_priv
db
func
help_category
help_keyword
help_relation
help_topic
host
proc
procs_priv
tables_priv
time_zone
time_zone_leap_second
time_zone_name
time_zone_transition
time_zone_transition_type
user
t1
t4
t2
t3
v1
select c,table_name from v1 
inner join information_schema.TABLES v2 on (v1.c=v2.table_name)
where v1.c like "t%";
c	table_name
TABLES	TABLES
TABLE_PRIVILEGES	TABLE_PRIVILEGES
TABLE_CONSTRAINTS	TABLE_CONSTRAINTS
tables_priv	tables_priv
time_zone	time_zone
time_zone_leap_second	time_zone_leap_second
time_zone_name	time_zone_name
time_zone_transition	time_zone_transition
time_zone_transition_type	time_zone_transition_type
t1	t1
t4	t4
t2	t2
t3	t3
select c,table_name from v1 
left join information_schema.TABLES v2 on (v1.c=v2.table_name)
where v1.c like "t%";
c	table_name
TABLES	TABLES
TABLE_PRIVILEGES	TABLE_PRIVILEGES
TABLE_CONSTRAINTS	TABLE_CONSTRAINTS
tables_priv	tables_priv
time_zone	time_zone
time_zone_leap_second	time_zone_leap_second
time_zone_name	time_zone_name
time_zone_transition	time_zone_transition
time_zone_transition_type	time_zone_transition_type
t1	t1
t4	t4
t2	t2
t3	t3
select c, v2.table_name from v1
right join information_schema.TABLES v2 on (v1.c=v2.table_name)
where v1.c like "t%";
c	table_name
TABLES	TABLES
TABLE_PRIVILEGES	TABLE_PRIVILEGES
TABLE_CONSTRAINTS	TABLE_CONSTRAINTS
tables_priv	tables_priv
time_zone	time_zone
time_zone_leap_second	time_zone_leap_second
time_zone_name	time_zone_name
time_zone_transition	time_zone_transition
time_zone_transition_type	time_zone_transition_type
t1	t1
t4	t4
t2	t2
t3	t3
select table_name from information_schema.TABLES
where table_schema = "mysqltest" and table_name like "t%";
table_name
t1
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
NULL	mysqltest	t1	1	mysqltest	string_data	1	b	A	NULL	NULL	NULL	YES	BTREE	
show keys from t3 where Key_name = "a_data";
Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment
t3	1	a_data	1	a	A	NULL	NULL	NULL	YES	BTREE	
show tables like 't%';
Tables_in_test (t%)
t2
t3
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
t2	MyISAM	10	Fixed	0	0	0	#	1024	0	NULL	#	#	NULL	latin1_swedish_ci	NULL		
t3	MyISAM	10	Fixed	0	0	0	#	1024	0	NULL	#	#	NULL	latin1_swedish_ci	NULL		
v1	NULL	NULL	NULL	NULL	NULL	NULL	#	NULL	NULL	NULL	#	#	NULL	NULL	NULL	NULL	view
show full columns from t3 like "a%";
Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment
a	int(11)	NULL	YES	MUL	NULL		select,insert,update,references	
show full columns from mysql.db like "Insert%";
Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment
Insert_priv	enum('N','Y')	utf8_general_ci	NO		N		select,insert,update,references	
show full columns from v1;
Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment
c	varchar(64)	utf8_general_ci	NO				select,insert,update,references	
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
NULL	mysqltest	t1	a	1	NULL	YES	int	NULL	NULL	11	0	NULL	NULL	int(11)			select,insert,update,references	
show columns from mysqltest.t1 where field like "%a%";
Field	Type	Null	Key	Default	Extra
a	int(11)	YES		NULL	
create view mysqltest.v1 (c) as select a from mysqltest.t1;
grant select (a) on mysqltest.t1 to mysqltest_2@localhost;
grant select on mysqltest.v1 to mysqltest_3;
select table_name, column_name, privileges from information_schema.columns 
where table_schema = 'mysqltest' and table_name = 't1';
table_name	column_name	privileges
t1	a	select
show columns from mysqltest.t1;
Field	Type	Null	Key	Default	Extra
a	int(11)	YES		NULL	
b	varchar(30)	YES	MUL	NULL	
select table_name, column_name, privileges from information_schema.columns
where table_schema = 'mysqltest' and table_name = 'v1';
table_name	column_name	privileges
v1	c	select
drop view v1, mysqltest.v1;
drop tables mysqltest.t4, mysqltest.t1, t2, t3;
drop database mysqltest;
select * from information_schema.CHARACTER_SETS
where CHARACTER_SET_NAME like 'latin1%';
CHARACTER_SET_NAME	DEFAULT_COLLATE_NAME	DESCRIPTION	MAXLEN
latin1	latin1_swedish_ci	ISO 8859-1 West European	1
SHOW CHARACTER SET LIKE 'latin1%';
Charset	Description	Default collation	Maxlen
latin1	ISO 8859-1 West European	latin1_swedish_ci	1
SHOW CHARACTER SET WHERE charset like 'latin1%';
Charset	Description	Default collation	Maxlen
latin1	ISO 8859-1 West European	latin1_swedish_ci	1
select * from information_schema.COLLATIONS
where COLLATION_NAME like 'latin1%';
COLLATION_NAME	CHARACTER_SET_NAME	ID	IS_DEFAULT	IS_COMPILED	SORTLEN
latin1_german1_ci	latin1	5			0
latin1_swedish_ci	latin1	8	Yes	Yes	1
latin1_danish_ci	latin1	15			0
latin1_german2_ci	latin1	31		Yes	2
latin1_bin	latin1	47		Yes	1
latin1_general_ci	latin1	48			0
latin1_general_cs	latin1	49			0
latin1_spanish_ci	latin1	94			0
SHOW COLLATION LIKE 'latin1%';
Collation	Charset	Id	Default	Compiled	Sortlen
latin1_german1_ci	latin1	5			0
latin1_swedish_ci	latin1	8	Yes	Yes	1
latin1_danish_ci	latin1	15			0
latin1_german2_ci	latin1	31		Yes	2
latin1_bin	latin1	47		Yes	1
latin1_general_ci	latin1	48			0
latin1_general_cs	latin1	49			0
latin1_spanish_ci	latin1	94			0
SHOW COLLATION WHERE collation like 'latin1%';
Collation	Charset	Id	Default	Compiled	Sortlen
latin1_german1_ci	latin1	5			0
latin1_swedish_ci	latin1	8	Yes	Yes	1
latin1_danish_ci	latin1	15			0
latin1_german2_ci	latin1	31		Yes	2
latin1_bin	latin1	47		Yes	1
latin1_general_ci	latin1	48			0
latin1_general_cs	latin1	49			0
latin1_spanish_ci	latin1	94			0
select * from information_schema.COLLATION_CHARACTER_SET_APPLICABILITY
where COLLATION_NAME like 'latin1%';
COLLATION_NAME	CHARACTER_SET_NAME
latin1_german1_ci	latin1
latin1_swedish_ci	latin1
latin1_danish_ci	latin1
latin1_german2_ci	latin1
latin1_bin	latin1
latin1_general_ci	latin1
latin1_general_cs	latin1
latin1_spanish_ci	latin1
create function sub1(i int) returns int
return i+1;
create procedure sel2()
begin
select * from t1;
select * from t2;
end|
select parameter_style, sql_data_access, dtd_identifier 
from information_schema.routines;
parameter_style	sql_data_access	dtd_identifier
SQL	CONTAINS SQL	NULL
SQL	CONTAINS SQL	int(11)
show procedure status;
Db	Name	Type	Definer	Modified	Created	Security_type	Comment
test	sel2	PROCEDURE	root@localhost	#	#	DEFINER	
show function status;
Db	Name	Type	Definer	Modified	Created	Security_type	Comment
test	sub1	FUNCTION	root@localhost	#	#	DEFINER	
select a.ROUTINE_NAME from information_schema.ROUTINES a,
information_schema.SCHEMATA b where
a.ROUTINE_SCHEMA = b.SCHEMA_NAME;
ROUTINE_NAME
sel2
sub1
explain select a.ROUTINE_NAME from information_schema.ROUTINES a,
information_schema.SCHEMATA b where
a.ROUTINE_SCHEMA = b.SCHEMA_NAME;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	#	ALL	NULL	NULL	NULL	NULL	2	
1	SIMPLE	#	ALL	NULL	NULL	NULL	NULL	2	Using where
select a.ROUTINE_NAME, b.name from information_schema.ROUTINES a,
mysql.proc b where a.ROUTINE_NAME = convert(b.name using utf8);
ROUTINE_NAME	name
sub1	sub1
sel2	sel2
select count(*) from information_schema.ROUTINES;
count(*)
2
select ROUTINE_NAME, ROUTINE_DEFINITION from information_schema.ROUTINES;
ROUTINE_NAME	ROUTINE_DEFINITION
show create function sub1;
ERROR 42000: FUNCTION sub1 does not exist
select ROUTINE_NAME, ROUTINE_DEFINITION from information_schema.ROUTINES;
ROUTINE_NAME	ROUTINE_DEFINITION
sel2	
sub1	
grant all privileges on test.* to mysqltest_1@localhost;
select ROUTINE_NAME, ROUTINE_DEFINITION from information_schema.ROUTINES;
ROUTINE_NAME	ROUTINE_DEFINITION
sel2	
sub1	
create function sub2(i int) returns int
return i+1;
select ROUTINE_NAME, ROUTINE_DEFINITION from information_schema.ROUTINES;
ROUTINE_NAME	ROUTINE_DEFINITION
sel2	
sub1	
sub2	return i+1
show create procedure sel2;
Procedure	sql_mode	Create Procedure
sel2		
show create function sub1;
Function	sql_mode	Create Function
sub1		
show create function sub2;
Function	sql_mode	Create Function
sub2		CREATE FUNCTION `test`.`sub2`(i int) RETURNS int(11)
return i+1
drop function sub2;
show create procedure sel2;
Procedure	sql_mode	Create Procedure
sel2		CREATE PROCEDURE `test`.`sel2`()
begin
select * from t1;
select * from t2;
end
create view v0 (c) as select schema_name from information_schema.schemata;
select * from v0;
c
information_schema
mysql
test
explain select * from v0;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	#	ALL	NULL	NULL	NULL	NULL	2	
create view v1 (c) as select table_name from information_schema.tables
where table_name="v1";
select * from v1;
c
v1
create view v2 (c) as select column_name from information_schema.columns
where table_name="v2";
select * from v2;
c
c
create view v3 (c) as select CHARACTER_SET_NAME from information_schema.character_sets
where CHARACTER_SET_NAME like "latin1%";
select * from v3;
c
latin1
create view v4 (c) as select COLLATION_NAME from information_schema.collations
where COLLATION_NAME like "latin1%";
select * from v4;
c
latin1_german1_ci
latin1_swedish_ci
latin1_danish_ci
latin1_german2_ci
latin1_bin
latin1_general_ci
latin1_general_cs
latin1_spanish_ci
show keys from v4;
Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment
select * from information_schema.views where TABLE_NAME like "v%";
TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	VIEW_DEFINITION	CHECK_OPTION	IS_UPDATABLE
NULL	test	v0	select sql_no_cache `schemata`.`SCHEMA_NAME` AS `c` from `information_schema`.`schemata`	NONE	NO
NULL	test	v1	select sql_no_cache `tables`.`TABLE_NAME` AS `c` from `information_schema`.`tables` where (`tables`.`TABLE_NAME` = _utf8'v1')	NONE	NO
NULL	test	v2	select sql_no_cache `columns`.`COLUMN_NAME` AS `c` from `information_schema`.`columns` where (`columns`.`TABLE_NAME` = _utf8'v2')	NONE	NO
NULL	test	v3	select sql_no_cache `character_sets`.`CHARACTER_SET_NAME` AS `c` from `information_schema`.`character_sets` where (`character_sets`.`CHARACTER_SET_NAME` like _utf8'latin1%')	NONE	NO
NULL	test	v4	select sql_no_cache `collations`.`COLLATION_NAME` AS `c` from `information_schema`.`collations` where (`collations`.`COLLATION_NAME` like _utf8'latin1%')	NONE	NO
drop view v0, v1, v2, v3, v4;
create table t1 (a int);
grant select,update,insert on t1 to mysqltest_1@localhost;
grant select (a), update (a),insert(a), references(a) on t1 to mysqltest_1@localhost;
grant all on test.* to mysqltest_1@localhost with grant option;
select * from information_schema.USER_PRIVILEGES where grantee like '%mysqltest_1%';
GRANTEE	TABLE_CATALOG	PRIVILEGE_TYPE	IS_GRANTABLE
'mysqltest_1'@'localhost'	NULL	USAGE	NO
select * from information_schema.SCHEMA_PRIVILEGES where grantee like '%mysqltest_1%';
GRANTEE	TABLE_CATALOG	TABLE_SCHEMA	PRIVILEGE_TYPE	IS_GRANTABLE
'mysqltest_1'@'localhost'	NULL	test	SELECT	YES
'mysqltest_1'@'localhost'	NULL	test	INSERT	YES
'mysqltest_1'@'localhost'	NULL	test	UPDATE	YES
'mysqltest_1'@'localhost'	NULL	test	DELETE	YES
'mysqltest_1'@'localhost'	NULL	test	CREATE	YES
'mysqltest_1'@'localhost'	NULL	test	DROP	YES
'mysqltest_1'@'localhost'	NULL	test	REFERENCES	YES
'mysqltest_1'@'localhost'	NULL	test	INDEX	YES
'mysqltest_1'@'localhost'	NULL	test	ALTER	YES
'mysqltest_1'@'localhost'	NULL	test	CREATE TEMPORARY TABLES	YES
'mysqltest_1'@'localhost'	NULL	test	LOCK TABLES	YES
'mysqltest_1'@'localhost'	NULL	test	EXECUTE	YES
'mysqltest_1'@'localhost'	NULL	test	CREATE VIEW	YES
'mysqltest_1'@'localhost'	NULL	test	SHOW VIEW	YES
'mysqltest_1'@'localhost'	NULL	test	CREATE ROUTINE	YES
'mysqltest_1'@'localhost'	NULL	test	ALTER ROUTINE	YES
select * from information_schema.TABLE_PRIVILEGES where grantee like '%mysqltest_1%';
GRANTEE	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	PRIVILEGE_TYPE	IS_GRANTABLE
'mysqltest_1'@'localhost'	NULL	test	t1	SELECT	NO
'mysqltest_1'@'localhost'	NULL	test	t1	INSERT	NO
'mysqltest_1'@'localhost'	NULL	test	t1	UPDATE	NO
select * from information_schema.COLUMN_PRIVILEGES where grantee like '%mysqltest_1%';
GRANTEE	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	PRIVILEGE_TYPE	IS_GRANTABLE
'mysqltest_1'@'localhost'	NULL	test	t1	a	SELECT	NO
'mysqltest_1'@'localhost'	NULL	test	t1	a	INSERT	NO
'mysqltest_1'@'localhost'	NULL	test	t1	a	UPDATE	NO
'mysqltest_1'@'localhost'	NULL	test	t1	a	REFERENCES	NO
delete from mysql.user where user like 'mysqltest%';
delete from mysql.db where user like 'mysqltest%';
delete from mysql.tables_priv where user like 'mysqltest%';
delete from mysql.columns_priv where user like 'mysqltest%';
flush privileges;
drop table t1;
create table t1 (a int null, primary key(a));
alter table t1 add constraint constraint_1 unique (a);
alter table t1 add constraint unique key_1(a);
alter table t1 add constraint constraint_2 unique key_2(a);
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) NOT NULL default '0',
  PRIMARY KEY  (`a`),
  UNIQUE KEY `constraint_1` (`a`),
  UNIQUE KEY `key_1` (`a`),
  UNIQUE KEY `key_2` (`a`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
select * from information_schema.TABLE_CONSTRAINTS where
TABLE_SCHEMA= "test";
CONSTRAINT_CATALOG	CONSTRAINT_SCHEMA	CONSTRAINT_NAME	TABLE_SCHEMA	TABLE_NAME	CONSTRAINT_TYPE
NULL	test	PRIMARY	test	t1	PRIMARY KEY
NULL	test	constraint_1	test	t1	UNIQUE
NULL	test	key_1	test	t1	UNIQUE
NULL	test	key_2	test	t1	UNIQUE
select * from information_schema.KEY_COLUMN_USAGE where
TABLE_SCHEMA= "test";
CONSTRAINT_CATALOG	CONSTRAINT_SCHEMA	CONSTRAINT_NAME	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	ORDINAL_POSITION	POSITION_IN_UNIQUE_CONSTRAINT
NULL	test	PRIMARY	NULL	test	t1	a	1	NULL
NULL	test	constraint_1	NULL	test	t1	a	1	NULL
NULL	test	key_1	NULL	test	t1	a	1	NULL
NULL	test	key_2	NULL	test	t1	a	1	NULL
select table_name from information_schema.TABLES where table_schema like "test%";
table_name
t1
select table_name,column_name from information_schema.COLUMNS where table_schema like "test%";
table_name	column_name
t1	a
select ROUTINE_NAME from information_schema.ROUTINES;
ROUTINE_NAME
sel2
sub1
delete from mysql.user where user='mysqltest_1';
drop table t1;
drop procedure sel2;
drop function sub1;
create table t1(a int);
create view v1 (c) as select a from t1 with check option;
create view v2 (c) as select a from t1 WITH LOCAL CHECK OPTION;
create view v3 (c) as select a from t1 WITH CASCADED CHECK OPTION;
select * from information_schema.views;
TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	VIEW_DEFINITION	CHECK_OPTION	IS_UPDATABLE
NULL	test	v1	select `test`.`t1`.`a` AS `c` from `test`.`t1`	CASCADED	YES
NULL	test	v2	select `test`.`t1`.`a` AS `c` from `test`.`t1`	LOCAL	YES
NULL	test	v3	select `test`.`t1`.`a` AS `c` from `test`.`t1`	CASCADED	YES
grant select (a) on test.t1 to joe@localhost with grant option;
select * from INFORMATION_SCHEMA.COLUMN_PRIVILEGES;
GRANTEE	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	PRIVILEGE_TYPE	IS_GRANTABLE
'joe'@'localhost'	NULL	test	t1	a	SELECT	YES
select * from INFORMATION_SCHEMA.TABLE_PRIVILEGES;
GRANTEE	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	PRIVILEGE_TYPE	IS_GRANTABLE
drop view v1, v2, v3;
drop table t1;
delete from mysql.user where user='joe';
delete from mysql.db where user='joe';
delete from mysql.tables_priv where user='joe';
delete from mysql.columns_priv where user='joe';
flush privileges;
create table t1 (a int not null auto_increment,b int, primary key (a));
insert into t1 values (1,1),(NULL,3),(NULL,4);
select AUTO_INCREMENT from information_schema.tables where table_name = 't1';
AUTO_INCREMENT
4
drop table t1;
create table t1 (s1 int);
insert into t1 values (0),(9),(0);
select s1 from t1 where s1 in (select version from
information_schema.tables) union select version from
information_schema.tables;
s1
0
10
drop table t1;
SHOW CREATE TABLE INFORMATION_SCHEMA.character_sets;
Table	Create Table
character_sets	CREATE TEMPORARY TABLE `character_sets` (
  `CHARACTER_SET_NAME` varchar(64) NOT NULL default '',
  `DEFAULT_COLLATE_NAME` varchar(64) NOT NULL default '',
  `DESCRIPTION` varchar(60) NOT NULL default '',
  `MAXLEN` bigint(3) NOT NULL default '0'
) ENGINE=MEMORY DEFAULT CHARSET=utf8
set names latin2;
SHOW CREATE TABLE INFORMATION_SCHEMA.character_sets;
Table	Create Table
character_sets	CREATE TEMPORARY TABLE `character_sets` (
  `CHARACTER_SET_NAME` varchar(64) NOT NULL default '',
  `DEFAULT_COLLATE_NAME` varchar(64) NOT NULL default '',
  `DESCRIPTION` varchar(60) NOT NULL default '',
  `MAXLEN` bigint(3) NOT NULL default '0'
) ENGINE=MEMORY DEFAULT CHARSET=utf8
set names latin1;
create table t1 select * from information_schema.CHARACTER_SETS
where CHARACTER_SET_NAME like "latin1";
select * from t1;
CHARACTER_SET_NAME	DEFAULT_COLLATE_NAME	DESCRIPTION	MAXLEN
latin1	latin1_swedish_ci	ISO 8859-1 West European	1
alter table t1 default character set utf8;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `CHARACTER_SET_NAME` varchar(64) NOT NULL default '',
  `DEFAULT_COLLATE_NAME` varchar(64) NOT NULL default '',
  `DESCRIPTION` varchar(60) NOT NULL default '',
  `MAXLEN` bigint(3) NOT NULL default '0'
) ENGINE=MyISAM DEFAULT CHARSET=utf8
drop table t1;
create view v1 as select * from information_schema.TABLES;
drop view v1;
create table t1(a NUMERIC(5,3), b NUMERIC(5,1), c float(5,2),
d NUMERIC(6,4), e float, f DECIMAL(6,3), g int(11), h DOUBLE(10,3),
i DOUBLE);
select COLUMN_NAME,COLUMN_TYPE, CHARACTER_MAXIMUM_LENGTH, 
CHARACTER_OCTET_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE
from information_schema.columns where table_name= 't1';
COLUMN_NAME	COLUMN_TYPE	CHARACTER_MAXIMUM_LENGTH	CHARACTER_OCTET_LENGTH	NUMERIC_PRECISION	NUMERIC_SCALE
a	decimal(5,3)	NULL	NULL	5	3
b	decimal(5,1)	NULL	NULL	5	1
c	float(5,2)	NULL	NULL	5	2
d	decimal(6,4)	NULL	NULL	6	4
e	float	NULL	NULL	12	NULL
f	decimal(6,3)	NULL	NULL	6	3
g	int(11)	NULL	NULL	11	0
h	double(10,3)	NULL	NULL	10	3
i	double	NULL	NULL	22	NULL
drop table t1;
create table t115 as select table_name, column_name, column_type
from information_schema.columns where table_name = 'proc';
select * from t115;
table_name	column_name	column_type
proc	db	char(64)
proc	name	char(64)
proc	type	enum('FUNCTION','PROCEDURE')
proc	specific_name	char(64)
proc	language	enum('SQL')
proc	sql_data_access	enum('CONTAINS_SQL','NO_SQL','READS_SQL_DATA','MODIFIES_SQL_DATA')
proc	is_deterministic	enum('YES','NO')
proc	security_type	enum('INVOKER','DEFINER')
proc	param_list	blob
proc	returns	char(64)
proc	body	blob
proc	definer	char(77)
proc	created	timestamp
proc	modified	timestamp
proc	sql_mode	set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','NOT_USED','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE')
proc	comment	char(64)
drop table t115;
create procedure p108 () begin declare c cursor for select data_type
from information_schema.columns;  open c; open c; end;//
call p108()//
ERROR 24000: Cursor is already open
drop procedure p108;
create view v1 as select A1.table_name from information_schema.TABLES A1
where table_name= "user";
select * from v1;
table_name
user
drop view v1;
create view vo as select 'a' union select 'a';
show index from vo;
Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment
select * from information_schema.TABLE_CONSTRAINTS where
TABLE_NAME= "vo";
CONSTRAINT_CATALOG	CONSTRAINT_SCHEMA	CONSTRAINT_NAME	TABLE_SCHEMA	TABLE_NAME	CONSTRAINT_TYPE
select * from information_schema.KEY_COLUMN_USAGE where
TABLE_NAME= "vo";
CONSTRAINT_CATALOG	CONSTRAINT_SCHEMA	CONSTRAINT_NAME	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	ORDINAL_POSITION	POSITION_IN_UNIQUE_CONSTRAINT
drop view vo;
select TABLE_NAME,TABLE_TYPE,ENGINE
from information_schema.tables 
where table_schema='information_schema' limit 2;
TABLE_NAME	TABLE_TYPE	ENGINE
SCHEMATA	TEMPORARY	MEMORY
TABLES	TEMPORARY	MEMORY
show tables from information_schema like "T%";
Tables_in_information_schema (T%)
TABLES
TABLE_PRIVILEGES
TABLE_CONSTRAINTS
create database information_schema;
ERROR HY000: Can't create database 'information_schema'; database exists
use information_schema;
show full tables like "T%";
Tables_in_information_schema (T%)	Table_type
TABLES	TEMPORARY
TABLE_PRIVILEGES	TEMPORARY
TABLE_CONSTRAINTS	TEMPORARY
create table t1(a int);
ERROR 42S02: Unknown table 't1' in information_schema
use test;
show tables;
Tables_in_test
use information_schema;
show tables like "T%";
Tables_in_information_schema (T%)
TABLES
TABLE_PRIVILEGES
TABLE_CONSTRAINTS
select table_name from tables where table_name='user';
table_name
user
select column_name, privileges from columns
where table_name='user' and column_name like '%o%';
column_name	privileges
Host	select,insert,update,references
Password	select,insert,update,references
Drop_priv	select,insert,update,references
Reload_priv	select,insert,update,references
Shutdown_priv	select,insert,update,references
Process_priv	select,insert,update,references
Show_db_priv	select,insert,update,references
Lock_tables_priv	select,insert,update,references
Show_view_priv	select,insert,update,references
Create_routine_priv	select,insert,update,references
Alter_routine_priv	select,insert,update,references
max_questions	select,insert,update,references
max_connections	select,insert,update,references
max_user_connections	select,insert,update,references
use test;
create function sub1(i int) returns int
return i+1;
create table t1(f1 int);
create view t2 (c) as select f1 from t1;
create view t3 (c) as select sub1(1);
create table t4(f1 int, KEY f1_key (f1));
drop table t1;
drop function sub1;
select table_name from information_schema.views
where table_schema='test';
table_name
Warnings:
Warning	1356	View 'test.t2' references invalid table(s) or column(s) or function(s)
Warning	1356	View 'test.t3' references invalid table(s) or column(s) or function(s)
select table_name from information_schema.views
where table_schema='test';
table_name
Warnings:
Warning	1356	View 'test.t2' references invalid table(s) or column(s) or function(s)
Warning	1356	View 'test.t3' references invalid table(s) or column(s) or function(s)
select column_name from information_schema.columns
where table_schema='test';
column_name
f1
Warnings:
Warning	1356	View 'test.t2' references invalid table(s) or column(s) or function(s)
Warning	1356	View 'test.t3' references invalid table(s) or column(s) or function(s)
select index_name from information_schema.statistics where table_schema='test';
index_name
f1_key
select constraint_name from information_schema.table_constraints
where table_schema='test';
constraint_name
drop view t2;
drop view t3;
drop table t4;
select * from information_schema.table_names;
ERROR 42S02: Unknown table 'table_names' in information_schema
select column_type from information_schema.columns
where table_schema="information_schema" and table_name="COLUMNS" and
(column_name="character_set_name" or column_name="collation_name");
column_type
varchar(64)
varchar(64)
select TABLE_ROWS from information_schema.tables where 
table_schema="information_schema" and table_name="COLUMNS";
TABLE_ROWS
NULL
select table_type from information_schema.tables
where table_schema="mysql" and table_name="user";
table_type
BASE TABLE
show open tables where `table` like "user";
Database	Table	In_use	Name_locked
mysql	user	0	0
show status where variable_name like "%database%";
Variable_name	Value
Com_show_databases	0
show variables where variable_name like "skip_show_databas";
Variable_name	Value
show global status like "Threads_running";
Variable_name	Value
Threads_running	1
create table t1(f1 int);
create table t2(f2 int);
create view v1 as select * from t1, t2;
set @got_val= (select count(*) from information_schema.columns);
drop view v1;
drop table t1, t2;
CREATE TABLE t_crashme ( f1 BIGINT);
CREATE VIEW a1 (t_CRASHME) AS SELECT f1 FROM t_crashme GROUP BY f1;
CREATE VIEW a2 AS SELECT t_CRASHME FROM a1;
count(*)
100
drop view a2, a1;
drop table t_crashme;
select table_schema,table_name, column_name from
information_schema.columns 
where data_type = 'longtext';
table_schema	table_name	column_name
information_schema	COLUMNS	COLUMN_TYPE
information_schema	ROUTINES	ROUTINE_DEFINITION
information_schema	ROUTINES	SQL_MODE
information_schema	VIEWS	VIEW_DEFINITION
select table_name, column_name, data_type from information_schema.columns
where data_type = 'datetime';
table_name	column_name	data_type
TABLES	CREATE_TIME	datetime
TABLES	UPDATE_TIME	datetime
TABLES	CHECK_TIME	datetime
ROUTINES	CREATED	datetime
ROUTINES	LAST_ALTERED	datetime
SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES A
WHERE NOT EXISTS 
(SELECT * FROM INFORMATION_SCHEMA.COLUMNS B
WHERE A.TABLE_SCHEMA = B.TABLE_SCHEMA
AND A.TABLE_NAME = B.TABLE_NAME);
COUNT(*)
0
create table t1
( x_bigint BIGINT,
x_integer INTEGER,
x_smallint SMALLINT,
x_decimal DECIMAL(5,3),
x_numeric NUMERIC(5,3),
x_real REAL,
x_float FLOAT,
x_double_precision DOUBLE PRECISION );
SELECT COLUMN_NAME, CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME= 't1';
COLUMN_NAME	CHARACTER_MAXIMUM_LENGTH	CHARACTER_OCTET_LENGTH
x_bigint	NULL	NULL
x_integer	NULL	NULL
x_smallint	NULL	NULL
x_decimal	NULL	NULL
x_numeric	NULL	NULL
x_real	NULL	NULL
x_float	NULL	NULL
x_double_precision	NULL	NULL
drop table t1;
create user mysqltest_4@localhost;
SELECT TABLE_NAME, COLUMN_NAME, PRIVILEGES FROM INFORMATION_SCHEMA.COLUMNS 
where COLUMN_NAME='TABLE_NAME';
TABLE_NAME	COLUMN_NAME	PRIVILEGES
TABLES	TABLE_NAME	select
COLUMNS	TABLE_NAME	select
STATISTICS	TABLE_NAME	select
VIEWS	TABLE_NAME	select
TABLE_PRIVILEGES	TABLE_NAME	select
COLUMN_PRIVILEGES	TABLE_NAME	select
TABLE_CONSTRAINTS	TABLE_NAME	select
KEY_COLUMN_USAGE	TABLE_NAME	select
delete from mysql.user where user='mysqltest_4';
flush privileges;
SELECT table_schema, count(*) FROM information_schema.TABLES GROUP BY TABLE_SCHEMA;
table_schema	count(*)
information_schema	15
mysql	17